Duplicate entry query on two tables

[tonyinwv]

I have read several of the postings and only one hit on my issue but
did not provide a fix. I have also tried many variations of many
suggestions to no success. I need the resultant output of the query
to list the "extra" record.
Here are the test tables that I am working with:

Table1
ID Account Dollar
1 101 10.00
2 102 20.00
3 102 20.00
4 103 35.00
5 104 30.00
6 105 20.00
7 102 20.00

Table2
ID Account Dollar
1 101 10.00
2 101 15.00
3 102 20.00
4 103 35.00
5 104 30.00
6 105 20.00
7 102 20.00

Running the query from Table1 on Table2 should result in the output of
one unique record for Account= 102 and Dollar=20....say, 2,102,20

Running the query from Table2 on Table1 should result in the output of
nothing because there are no extra records in Table2 that cannot be
offset by a record in Table1.

Another way of saying it, there are three records in Table1 for
Account=102 and Dollar=20 but there are only two records for
Account=102 and Dollar=20 in Table2. I am looking to output the
"extra" record.

About the real data I am working with: They are account numbers and
dollar amounts that can be duplicated within the table. There are no
other unique fields such as date or time. So, I allowed Access to
assign the primary key.

I hope I didn't ramble and hope this makes sense. Any help is greatly
appreciated.

T.

 

[Guest]

If you want to return just one row where there are extra matching rows in
Table1, regardless of how many extra rows there might be, then this will do
it:

SELECT Account, Dollar
FROM Table1
GROUP BY Account,Dollar
HAVING COUNT(*) >
(SELECT COUNT(*)
FROM Table2
WHERE Table2.Account = Table1.Account
AND Table2.Dollar = Table1.Dollar);

You can't return specific rows as, the primary key being an arbitrary value,
there is nothing to say which rows are the 'extra' ones. You can however
include a column to say how many extra rows there are for each account/dollar
combination with:

SELECT Account, Dollar,
COUNT(*) -
(SELECT COUNT(*)
FROM Table2
WHERE Table2.Account = Table1.Account
AND Table2.Dollar = Table1.Dollar) AS NumberOfExtras
FROM Table1
GROUP BY Account,Dollar
HAVING COUNT(*) >
(SELECT COUNT(*)
FROM Table2
WHERE Table2.Account = Table1.Account
AND Table2.Dollar = Table1.Dollar);

Ken Sheridan
Stafford, England

 

[tonyinwv]

Ken;

Thanks a bundle! Works like a charm. Takes a few minutes to run but I
have two tables with over 13000 entries each. You and I have saved my
client endless hours of manual reconcilement. And I will look like a
hero on Monday morning . Thanks for the help.

I was looking through some posts last night and your code will provide
a solution to a fellow with an identical problem. Do you care if I cut
and paste to his post, giving you the credit, of course?