Query help

[Lars Brownie]

I have 2 tables with identical structure and different data:
Table 1 Fld1 Fld2 Fld3
1 x A
2 y A
Table 2 Fld1 Fld2 Fld3
1 x B
3 y B
Fld3 in table 1 is always A, in table 2 it's always B.
I need to merge those tables and get one result table like this
Fld1 Fld2 Fld3
1 x A,B
2 y A
3 y B

So I need to get all unique values of fld1 and fld2, if they only reside in
one of the tables or in both tables. Also, if they reside in both tables the
values of Fld3 should be merged (A,B).

Is there some clever query that would accomplish this?

Thanks in advance,
Lars

 

[John W. Vinson/MVP]

I have 2 tables with identical structure and different data:
Table 1 Fld1 Fld2 Fld3
1 x A
2 y A
Table 2 Fld1 Fld2 Fld3
1 x B
3 y B
Fld3 in table 1 is always A, in table 2 it's always B.
I need to merge those tables and get one result table like this
Fld1 Fld2 Fld3
1 x A,B
2 y A
3 y B

So I need to get all unique values of fld1 and fld2, if they only reside in
one of the tables or in both tables. Also, if they reside in both tables the
values of Fld3 should be merged (A,B).

Is there some clever query that would accomplish this?

I'd suggest creating Table3 with the same fields and run three append
queries in succession. First append Table1 (actually, you could just
make a copy of Table1 and name it Table3 instead). Then run an append
query to append the records in Table2 which are NOT in table1; use the
"Unmatched Query Wizard" to create such a query and then turn it into
an append.

Then use a query joining Table1 to Table2:

INSERT INTO Table3 (Fld1, Fld2, Fld3)
SELECT Table1.Fld1, Table1.Fld2, "A,B"
FROM Table1 INNER JOIN Table2
ON Table1.Fld1 = Table2.Fld1;

I'm not sure what role Fld2 plays though: the above implies that if
Fld1 matches then Fld2 will match too. What result do you want if
Table1 contains "10; ThisValue; A" and Table2 contains "10; ThatValue;
B"? Do you want ThisValue, or ThatValue, or "ThisValue, ThatValue", or
two new records, one for each?