Combining Values for a Report

[Tom]

I have two tables, examples as follows:

1. Year 1 Table.....

Category Value
AAA 12
BBB 678
CCC 89
EEE 65
FFF 56


2.Year 2 Table.....

Category Value
BBB 68
CCC 89
DDD 1
EEE 32
GGG 11

At present neither table has a primary key or index.

I want combine the values in a report that would look like
this...

Report on Values

Year 1 Year 2

AAA 12 0
BBB 678 68
CCC 89 89
DDD 0 1
EEE 65 32
FFF 56 0
GGG 0 11

Please advise on how I would construct a query that would
combine the values from the tables to produce the report,
without losing lines on the report where there are no
values in the table (e.g. GGG in Year 1).

Any help would be greatly appreciated.
Thank you in advance.

Tom

 

[John Verhagen]

Try using these 2 queries:
Call this one qYearUnion:
SELECT Category FROM tblYear1
UNION SELECT Category From tblYear2;

Then use:
SELECT qYearUnion.Category,
IIf(nz([tblYear1].[value])="",0,[tblYear1].[value]) AS Year1,
IIf(nz([tblYear2].[value])="",0,[tblYear2].[value]) AS Year2
FROM (qYearUnion LEFT JOIN tblYear1 ON qYearUnion.Category =
tblYear1.Category) LEFT JOIN tblYear2 ON qYearUnion.Category =
tblYear2.Category;