Duplicate entry query on two tables

T

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.
 
K

Ken Sheridan

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
 
T

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top