Solved

Find duplicate records on Multiple fields

Posted on 2008-06-10
7
4,102 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

687 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