Compare two tabels

Ghada

New member
Local time
Today, 18:34
Joined
Jan 1, 2022
Messages
2
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:
MonthDOcTrnNoDRCR
9​
JRVJRV2455
0​
3150​
7​
JRVJRV2232
0​
3209​
7​
JRVJRV2259
0​
3209​
1​
JRVJRV1571
0​
3215​
6​
JRVJRV2180
0​
3317​
6​
JRVJRV1571
0​
3430​

Table B:
MonthDOCVoucherDR
10GJVGJV-843
3150​
6GJVGJV-694
3209​
1GJVGJV-177
3215​
6GJVGJV-656
3430​

Result needed is to return values from table A not in Table B:
MonthDOcTrnNoDRCR
7​
JRVJRV2259
0​
3209​
6​
JRVJRV2180
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?
 
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.
 
You do not even say what columns you are comparing???? :(
 
I would do this in parts.
Make two group by queries. Group on CR and and get the count per group.
Like:
Code:
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
Code:
SELECT qryGroupA.CR
FROM qryGroupA LEFT JOIN qryGroupB ON qryGroupA.CR = qryGroupB.CR
WHERE
  qryGroupA.CRCount)>[qryGroupB].[CRCount] OR qryGroupB.CR Is Null
 
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.
 
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.
 
thank you for the ideas i will try them (y)
 

Users who are viewing this thread

Back
Top Bottom