Solved

Find duplicate records on Multiple fields

Posted on 2008-06-10
7
4,098 Views
Last Modified: 2012-06-27
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
Comment
Question by:bluefeet10
  • 4
  • 3
7 Comments
 
LVL 19

Expert Comment

by:frankytee
ID: 21751148
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
 

Author Comment

by:bluefeet10
ID: 21751241
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
 
LVL 19

Expert Comment

by:frankytee
ID: 21751422
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:bluefeet10
ID: 21751554
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
 
LVL 19

Accepted Solution

by:
frankytee earned 500 total points
ID: 21751656
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
 

Author Closing Comment

by:bluefeet10
ID: 31465731
Thanks for the help.
0
 
LVL 19

Expert Comment

by:frankytee
ID: 21751872
you're welcome
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now