problem making an query?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can any one help me with the following (problem)?

I have two tables each with one text field. Table A has 15100 records and
table B has 14700 records.

I want to compare the two tables want to know 3 things:
1. which records are the same in both tables.
2. which record are only in table A and not in table B.
3. which records are only in table B and not in table A.

Can I do this with make a table query?

Thanks
Nico
 
Nico

?With a single query? How would you know which records belonged to which
condition?

You can use the query wizard to find records in one table not in another
(see Unmatched) -- #2 & #3.

You can create a query in design view, bring in both tables, join them on
their common field -- this shows all where they match.

Regards

Jeff Boyce
Microsoft Ofiice/Access MVP
 
These four queries will do it. Edit query replacing field name PK with your
field name.
[1-Query_1] ---
SELECT [Table A].PK AS A, [Table B].PK AS B
FROM [Table A] RIGHT JOIN [Table B] ON [Table A].PK = [Table B].PK
GROUP BY [Table A].PK, [Table B].PK;

[1-Query_2] ---
SELECT [Table A].PK AS A, [Table B].PK AS B
FROM [Table A] RIGHT JOIN [Table B] ON [Table A].PK = [Table B].PK
GROUP BY [Table A].PK, [Table B].PK;

[1-Query_3] ---
SELECT [1-Query_1].[A], [1-Query_1].
FROM [1-Query_1]
UNION ALL SELECT [1-Query_2].[A], [1-Query_2].
FROM [1-Query_2];

SELECT [1-Query_3].A, [1-Query_3].B, IIf([A]=,"Both",IIf([A] Is Not Null
And Is Null,"Table A","Table B")) AS Expr1
FROM [1-Query_3]
GROUP BY [1-Query_3].A, [1-Query_3].B, IIf([A]=,"Both",IIf([A] Is Not
Null And Is Null,"Table A","Table B"));
 
Back
Top