Query-finding umatched data with duplicates

[Solv123]

I am trying to compare two tables to find what does not match. However each
table may have duplicate items but I need each item to have its’own match.
The Unmatch query does not work

For example
Table 1
A
A
A
B
Tabe2:
A
A
B
C
I want my result to show
A
C
If I run the unmatch query my result is just C.

 

[John Spencer]

What do you want returned if 5 records were
A A A A A in Table 1
and only two in table 2
A A in Table 2?

Do you want A returned or do you want A A A returned?

The first is much easier than the second. Assuming that you have followed the
naming conventions and your table and field names contain NO spaces the first
is accomplished by using two sub-queries in the FROM clause.

SELECT T1.TheField, T1.fCount
FROM
(SELECT TheField, Count(TheField) as fCount FROM Table1 GROUP BY TheField)
as T1
LEFT JOIN
(SELECT TheField, Count(TheField) as fCount FROM Table2 GROUP BY TheField)
as T2
ON T1.TheField = T2.TheField And T1.fCount = T2.fCount
WHERE T2.TheField Is Null

If you don't know how to do this using the above.
Build two queries (one on each table) that returns the field and the count of
the field.
Then use the unmatched query wizard and the two queries as its source.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Daryl S]

Assuming you don't care with of the 'A' records in table 1 match to 'A'
records in table 2, and you just want to know that there is an extra 'A'
record in table 1, then you can do the following:

Set up 2 queries (one for each table) that is grouped by the field you are
interested in, with a count of those records. Results would look like this
for your example:

tbl1Field CountOftbl1Field
A 3
B 1

tbl2Field CountOftbl2Field
A 2
B 1
C 1

Then write a third query with the following SQL (assuming the queries above
are named qry_Table1Counts and qry_Table2Counts:

SELECT qry_Table2Counts.tbl2Field, qry_Table2Counts.CountOftbl2Field,
qry_Table1Counts.tbl1Field, qry_Table1Counts.CountOftbl1Field
FROM qry_Table2Counts LEFT JOIN qry_Table1Counts ON
qry_Table2Counts.tbl2Field = qry_Table1Counts.tbl1Field
WHERE (((qry_Table1Counts.CountOftbl1Field)<>[CountOftbl2Field])) OR
(((qry_Table1Counts.CountOftbl1Field) Is Null));

You can uncheck the 'Show' boxes for all but the first field if you only
want to see the 'A' and 'C'.

 

[Solv123]

Thank you so much. I started on the right track this morning creating two
queries to count each record but I never would have figured out the Where
statement. That is awesome. I may have more questions as the database
progresses. Thanks for your help!!
--
Jul

Assuming you don't care with of the 'A' records in table 1 match to 'A'
records in table 2, and you just want to know that there is an extra 'A'
record in table 1, then you can do the following:

Set up 2 queries (one for each table) that is grouped by the field you are
interested in, with a count of those records. Results would look like this
for your example:

tbl1Field CountOftbl1Field
A 3
B 1

tbl2Field CountOftbl2Field
A 2
B 1
C 1

Then write a third query with the following SQL (assuming the queries above
are named qry_Table1Counts and qry_Table2Counts:

SELECT qry_Table2Counts.tbl2Field, qry_Table2Counts.CountOftbl2Field,
qry_Table1Counts.tbl1Field, qry_Table1Counts.CountOftbl1Field
FROM qry_Table2Counts LEFT JOIN qry_Table1Counts ON
qry_Table2Counts.tbl2Field = qry_Table1Counts.tbl1Field
WHERE (((qry_Table1Counts.CountOftbl1Field)<>[CountOftbl2Field])) OR
(((qry_Table1Counts.CountOftbl1Field) Is Null));

You can uncheck the 'Show' boxes for all but the first field if you only
want to see the 'A' and 'C'.

--
Daryl S

I am trying to compare two tables to find what does not match. However each
table may have duplicate items but I need each item to have its’own match.
The Unmatch query does not work

For example
Table 1
A
A
A
B
Tabe2:
A
A
B
C
I want my result to show
A
C
If I run the unmatch query my result is just C.

 

[Solv123]

I am actually looking for the latter, A A A to be returned. Thanks.

 

[John Spencer]

Ok to get multiple records returned you would need an auxiliary table
tblNumbers with one field SequenceNumber. Sequence number would contain the
numbers from 1 to n (n being the largest number of repeats you expect)

First query would be as originally designated.
SELECT T1.TheField, T1.fCount
FROM
(SELECT TheField, Count(TheField) as fCount FROM Table1 GROUP BY TheField)
as T1
LEFT JOIN
(SELECT TheField, Count(TheField) as fCount FROM Table2 GROUP BY TheField)
as T2
ON T1.TheField = T2.TheField And T1.fCount = T2.fCount
WHERE T2.TheField Is Null

Using that saved query

SELECT Q.TheField
FROM TheSavedQuery as Q , tblNumbers
WHERE tblNumbers.SequenceNumber <= q.fCount

You might be able to do that all in one query

SELECT Q.TheField
FROM (SELECT T1.TheField, T1.fCount
FROM
(SELECT TheField, Count(TheField) as fCount
FROM Table1
GROUP BY TheField) as T1
LEFT JOIN
(SELECT TheField, Count(TheField) as fCount
FROM Table2
GROUP BY TheField) as T2
ON T1.TheField = T2.TheField And T1.fCount = T2.fCount
WHERE T2.TheField Is Null) as Q , tblNumbers
WHERE tblNumbers.SequenceNumber <= q.fCount


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County