I know there are a couple of questions regarding this Access Error message, but they are not related in that my query doesn't use the DISTINCT Key word.
I have two similar queries, one contains credits and the other contains debits. They are grouped by month and by category.
Ultimately I want a full outer join on these two tables so that I can subtract them to get a resulting balance for each month in each category.
However Access doesn't allow full outer joins so I need to do a Right OUTER UNION LEFT OUTER WHERE Null.
I am now trying to do the RIGHT OUTER join on the Month and Category ID fields. When I do the outer join on one field, it works as expected. When I do it on the other field it works as expected but when I join on both fields I get "The field is too small to accept the amount of data you attempted to add"
Table 1:
Produced by:
SELECT [transactions by category].[categoryid] AS CategoryID,
Format([account transactions].[transaction date], "mm/yy") AS MonthYear,
Nz(SUM([transactions by category].[amount]), 0) AS
[Category Total]
FROM [transactions by category]
INNER JOIN [account transactions]
ON [account transactions].[id] =
[transactions by category].[transactionid]
WHERE [account transactions].[transaction type] <> 8
GROUP BY Format([account transactions].[transaction date], "mm/yy"),
[transactions by category].[categoryid];
Table 2:
Produced by:
SELECT [transactions by category].[categoryid],
Format([account transactions].[transaction date], "mm/yy") AS MonthYear,
Nz(SUM([transactions by category].[amount]), 0) AS
[Category Total]
FROM [transactions by category]
INNER JOIN [account transactions]
ON [account transactions].[id] =
[transactions by category].[transactionid]
WHERE [account transactions].[transaction type] = 8
GROUP BY Format([account transactions].[transaction date], "mm/yy"),
[transactions by category].[categoryid];
The Right Join that gives me the error:
SELECT * FROM
((SELECT [transactions by category].[categoryid],
Format([account transactions].[transaction date], "mm/yy")
AS MonthYear,
Nz(SUM([transactions by category].[amount]), 0) AS [Category Total]
FROM [transactions by category]
INNER JOIN [account transactions]
ON [account transactions].[id] =
[transactions by category].[transactionid]
WHERE [account transactions].[transaction type] = 8
GROUP BY Format([account transactions].[transaction date], "mm/yy"),
[transactions by category].[categoryid]) AS [Category Returns]
RIGHT JOIN
(SELECT [transactions by category].[categoryid] AS CategoryID,
Format([account transactions].[transaction date], "mm/yy")
AS MonthYear,
Nz(SUM([transactions by category].[amount]), 0) AS [Category Total]
FROM [transactions by category]
INNER JOIN [account transactions]
ON [account transactions].[id] =
[transactions by category].[transactionid]
WHERE [account transactions].[transaction type] <> 8
GROUP BY Format([account transactions].[transaction date], "mm/yy"),
[transactions by category].[categoryid]) AS [Category Debits]
ON [Category Returns].[categoryid] = [Category Debits].[categoryid]
AND [Category Returns].[monthyear] = [Category Debits].[monthyear] );
It seems like this error occurs with text fields. When I use Format does the MonthYear field become a text field? Even still it is only 5 characters long. Plus the join works when I join only on the MonthYear columns, but only fails when I join on both fields.
