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

 

[pietlinden]

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

you should be able to write a union query and then create a distinct
query based on your union query. Honestly can't remember if it's
possible to do both (distinct and union) operations in the same query.

 

[pietlinden]

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

Forgot one thing... I used to have to do union queries all day long
not very long ago. The easiest way to do this is to create the first
query, then copy the SQL to the clipboard and replace the old table
name with the new one and then slap a " UNION " between the two
select statements.

 

[KARL DEWEY]

UNTESTED ----
First the union query named Merge_Query like this --
SELECT Fld1, Fld2, Fld3 AS Fld0, Null AS Fld9
FROM [Table 1]
UNION ALL SELECT Fld1, Fld2, Null AS Fld0, Fld3 AS Fld9
FROM [Table 1];

Then a totals query --
SELECT Fld1, Fld2, IIF([Fld0] Is Null, [Fld9], IIF([Fld9] Is Null, [Fld0],
[Fld0] & ", " & [Fld9])) AS [FldX]
FROM Merge_Query
GROUP BY Fld1, Fld2, IIF([Fld0] Is Null, [Fld9], IIF([Fld9] Is Null,
[Fld0], [Fld0] & ", " & [Fld9]));

 

[Lars Brownie]

Thanks Piet, Karl,
I got it to work with a union and totals query.
Lars

UNTESTED ----
First the union query named Merge_Query like this --
SELECT Fld1, Fld2, Fld3 AS Fld0, Null AS Fld9
FROM [Table 1]
UNION ALL SELECT Fld1, Fld2, Null AS Fld0, Fld3 AS Fld9
FROM [Table 1];

Then a totals query --
SELECT Fld1, Fld2, IIF([Fld0] Is Null, [Fld9], IIF([Fld9] Is Null, [Fld0],
[Fld0] & ", " & [Fld9])) AS [FldX]
FROM Merge_Query
GROUP BY Fld1, Fld2, IIF([Fld0] Is Null, [Fld9], IIF([Fld9] Is Null,
[Fld0], [Fld0] & ", " & [Fld9]));

--
KARL DEWEY
Build a little - Test a little

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