Compare two tabels

[Ghada]

Hi,
I have two different tables and I want to compare a column in table A to a column in table B, and return the values that are only in table A not in table B.
including the values that are duplicated in table A but not duplicated in table B.

I have used the unmatched query wizard but it does not show the difference in duplicated values.

for example:
Table A:

Month	DOc	TrnNo	DR	CR
9​	JRV	JRV2455	0​	3150​
7​	JRV	JRV2232	0​	3209​
7​	JRV	JRV2259	0​	3209​
1​	JRV	JRV1571	0​	3215​
6​	JRV	JRV2180	0​	3317​
6​	JRV	JRV1571	0​	3430​

Table B:

Month	DOC	Voucher	DR
10	GJV	GJV-843	3150​
6	GJV	GJV-694	3209​
1	GJV	GJV-177	3215​
6	GJV	GJV-656	3430​

Result needed is to return values from table A not in Table B:

Month	DOc	TrnNo	DR	CR
7​	JRV	JRV2259	0​	3209​
6​	JRV	JRV2180	0​	3317​

the unmatched query wizard does not return the (3209) because it considers it as available in table B.

Can you help me with this?

 

[jdraw]

You are likely to get more help/advice, if:

You provide the tables and values as data
Provide the SQL of the query

I think 3209 is in table B, so a clear description of your criteria would help.

Welcome to AWF and Happy New Year.

 

[Gasman]

You do not even say what columns you are comparing????

 

[MajP]

I would do this in parts.
Make two group by queries. Group on CR and and get the count per group.
Like:

SELECT tblA.CR, Count(tblA.CR) AS CRCount
FROM tblA
GROUP BY tblA.CR;

Then do an outer join from qryGrouplA to qryGropuB where A.CRCount > = B.CRCount and B.CR is Null

SELECT qryGroupA.CR
FROM qryGroupA LEFT JOIN qryGroupB ON qryGroupA.CR = qryGroupB.CR
WHERE
  qryGroupA.CRCount)>[qryGroupB].[CRCount] OR qryGroupB.CR Is Null

 

[The_Doc_Man]

The problem might be better solved by dividing it at first and re-merging it.

As you correctly mentioned, you can run an "Unmatched" query to get your "return the values that are only in table A not in table B" case. So make a query that returns what you want ONLY for the unmatched cases. Then build a query based on a COUNT of whatever is your key for duplication and only select those counts >1. Finally, merge the two result-sets using a UNION query. The "divide and conquer" method is easier than trying to do this all in one query, and you can then put the parts back together again.

 

[Pat Hartman]

The unmatched query wizard joins on only a single column. Once you have built the query using the wizard, open the query in design view and include the second column in the join and group by.

 

[Ghada]

thank you for the ideas i will try them