I have data like:
Table1 Table2
Fld1 Fld1
A A
B C
E D
F F

I would like to make a query that gives me

ResultTable with 2 fields:
InTbl1 InTbl2
A A
B
C
D
E
F F

Is this possible to do in one query?
Thank you.
John

You will need to use 2 queries.

This gives you each unique value in either table:
SELECT F1 FROM Table1
UNION SELECT F1 FROM Table2;

Now outer-join your origional tables to this query to get the result you
wanted:
SELECT Table1.F1, Table2.F1
FROM (Query1 LEFT JOIN Table1 ON Query1.F1 = Table1.F1)
LEFT JOIN Table2 ON Query1.F1 = Table2.F1;

It may be possible to do that with a subquery, but I think it would be more
stable and efficient as a 2-step process.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John J." <(E-Mail Removed)> wrote in message
news:gcshvp$i52$(E-Mail Removed)...
>I have data like:
> Table1 Table2
> Fld1 Fld1
> A A
> B C
> E D
> F F
>
> I would like to make a query that gives me
>
> ResultTable with 2 fields:
> InTbl1 InTbl2
> A A
> B
> C
> D
> E
> F F
>
> Is this possible to do in one query?
> Thank you.
> John

The operator you're looking for is Full Outer Join, which does exactly what
you asked for. SQL Server supports this operator, but Access does not. You
could write it as a union of 2 subqueries (untested syntax):

Select T1.Fld1 as InTbl1, T2.Fld1 as InTbl2
From Table1 as T1 Left Join Table2 as T2 On T1.Fld1=T2.Fld1

Union All

Select Null, T2.Fld1
From Table2 as T2
Where Not Exists (Select * From Table1 as T1 Where T1.Fld1=T2.Fld1)

"John J." <(E-Mail Removed)> wrote in message
news:gcshvp$i52$(E-Mail Removed)...
>I have data like:
> Table1 Table2
> Fld1 Fld1
> A A
> B C
> E D
> F F
>
> I would like to make a query that gives me
>
> ResultTable with 2 fields:
> InTbl1 InTbl2
> A A
> B
> C
> D
> E
> F F
>
> Is this possible to do in one query?

Excellent. Thanks!

"Allen Browne" <(E-Mail Removed)> schreef in bericht
news:(E-Mail Removed)...
> You will need to use 2 queries.
>
> This gives you each unique value in either table:
> SELECT F1 FROM Table1
> UNION SELECT F1 FROM Table2;
>
> Now outer-join your origional tables to this query to get the result you
> wanted:
> SELECT Table1.F1, Table2.F1
> FROM (Query1 LEFT JOIN Table1 ON Query1.F1 = Table1.F1)
> LEFT JOIN Table2 ON Query1.F1 = Table2.F1;
>
> It may be possible to do that with a subquery, but I think it would be
> more stable and efficient as a 2-step process.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "John J." <(E-Mail Removed)> wrote in message
> news:gcshvp$i52$(E-Mail Removed)...
>>I have data like:
>> Table1 Table2
>> Fld1 Fld1
>> A A
>> B C
>> E D
>> F F
>>
>> I would like to make a query that gives me
>>
>> ResultTable with 2 fields:
>> InTbl1 InTbl2
>> A A
>> B
>> C
>> D
>> E
>> F F
>>
>> Is this possible to do in one query?
>> Thank you.
>> John
>

Thanks! Works as well.

"Paul Shapiro" <(E-Mail Removed)> schreef in bericht
news:(E-Mail Removed)...
> The operator you're looking for is Full Outer Join, which does exactly
> what you asked for. SQL Server supports this operator, but Access does
> not. You could write it as a union of 2 subqueries (untested syntax):
>
> Select T1.Fld1 as InTbl1, T2.Fld1 as InTbl2
> From Table1 as T1 Left Join Table2 as T2 On T1.Fld1=T2.Fld1
>
> Union All
>
> Select Null, T2.Fld1
> From Table2 as T2
> Where Not Exists (Select * From Table1 as T1 Where T1.Fld1=T2.Fld1)
>
> "John J." <(E-Mail Removed)> wrote in message
> news:gcshvp$i52$(E-Mail Removed)...
>>I have data like:
>> Table1 Table2
>> Fld1 Fld1
>> A A
>> B C
>> E D
>> F F
>>
>> I would like to make a query that gives me
>>
>> ResultTable with 2 fields:
>> InTbl1 InTbl2
>> A A
>> B
>> C
>> D
>> E
>> F F
>>
>> Is this possible to do in one query?
>