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
Solved

Find duplicate records on Multiple fields

Posted on 2008-06-10
7
4,099 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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…

861 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