• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4106
  • Last Modified:

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

0
bluefeet10
Asked:
bluefeet10
  • 4
  • 3
1 Solution
 
frankyteeCommented:
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
0
 
bluefeet10Author Commented:
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.
0
 
frankyteeCommented:
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
bluefeet10Author Commented:
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.

0
 
frankyteeCommented:
try adding Destacct to your "duplicate" query (or create a new query) and then join to this query:
SELECT First(History.Account) AS Account, history.DestAcct,  Count(History.Account) AS NumberOfDups
FROM History, VARA
WHERE History.[Proc Date] >= VARA.ImportDate5
GROUP BY History.Account, history.DestAcct
HAVING (((Count(History.Account))>1));
0
 
bluefeet10Author Commented:
Thanks for the help.
0
 
frankyteeCommented:
you're welcome
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now