asked on

Find duplicate records on Multiple fields

Hi Everyone-
I have a Microsoft Access data that has a table that contains Account History data, so an account can appear in the table multiple times because it could have a record for each day something happened to it. I need to be able to pull from the table a list of the accounts that have sent something to the same destination multiple times for the past 5 days.

My initial query was this using an INNER JOIN of the duplicate query the query wizard created in Access.
SELECT History.*
FROM VARA, HistoryINNER JOIN [xxxFind duplicates for History] ON History.Account = [xxxFind duplicates for History].Account
WHERE (((WireHistory.[Proc Date])>=[VARA].[Date5]))
ORDER BY Account

This gets me the list of all accounts that appear multiple times in the last 5 days. But I need to filter this more, I need to get a list of the account that appear in the [xxxFind duplicates for History] that have sent something to the same destination. I can't figure out how to add this to the above query.

Any help would be appreciated.

Thanks

frankytee

is destination a field in your table and which one?
and your sql is wrong
>FROM VARA, HistoryINNER JOIN
what is VARA, History?
and post details on the table relations

bluefeet10

ASKER

Sorry for the oversight in my explanation.

The VARA table contains the date range that I need, since I need that last 5 business days, this table contains the date. The History table contains the Account info and destination info. When I pasted the query it pasted wrong below is the corrected query:

SELECT History.*
FROM VARA, History INNER JOIN [xxxFind duplicates for History] ON History.Account = [xxxFind duplicates for History].Account
WHERE (((History.[Proc Date])>=[VARA].[ImportDate5]))
ORDER BY History.Account;

The destination field is in the History table, it is called DestAcct.

frankytee

what is the sql for [[xxxFind duplicates for History]
and why does ImportDate5 have a 5 in its name?
its a bit hard to answer your question as i'm finding your naming convention and table relations confusing.
is VARA a "lookup" table with date ranges
post the fundamental table relations (1 to many etc) and the critica fields involved.

bluefeet10

ASKER

Let me try to explain this again. I have 2 tables, one table (VARA) contains a set of dates - the past 5 days. VARA.ImportDate5 is the fifth business day. The other table is History - this table contains the Account info, including Account #, DestAcct, date of trans, and other info.

I need to be able to pull from the 2 tables a list of accounts that have sent a Transaction to the same Dest Acct multiple times over the previous 5 days.

I used the [xxxFind duplicates for History] to find the duplicate accounts present in the over the previous 5 days. Here is the [xxxFind duplicates for History] query:
SELECT First(History.Account) AS Account, Count(History.Account) AS NumberOfDups
FROM History, VARA
WHERE History.[Proc Date] >= VARA.ImportDate5
GROUP BY History.Account
HAVING (((Count(History.Account))>1));

The query above as well as the initial one I posted work and pull the accounts that appear multiple times over the previous 5 days. However, now I adjust this query to only show the accounts that appear multiple times and have sent a trans to the same DestAcct over the previous 5 days. But I do not know how to add this to the first query that I posted.

ASKER CERTIFIED SOLUTION

frankytee

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

bluefeet10

ASKER

Thanks for the help.

frankytee

you're welcome