Finding Duplicates Based on Two Columns

[Guest]

I have two columns in my database that I want to use as criteria for finding
duplicates. One column contains account numbers and the other names of
cities. I want to find instances where if one city, which we will call "New
York", touches an account, and then any of the other cities touch that same
account, then give me that account number in the out put records. If no other
city touched that account, then I don't want any records in the output. I
will also be including transaction dates and phone numbers in the final
output if that matters. I'm thinking this will be some type of Duplicate
Query but I can't figure it out. Any thoughts?

 

[Roger Carlson]

Create a Duplicates Query with the Wizard, use only one of your fields. Then
manually edit the query to join the table on the second field.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Thanks for the help but I don't understand, sorry. I created a duplicate
query based on the account numbers, and that filtered out any nonduplicate
account numbers. But How do I get it to give me those that have been touched
by my "New York" office and the other office that touched them as well? I
tried putting like"New York" in the criteria, but it only gives me the New
York records, and not the other office that touched that same account.

Thanks,

Robert

 

[Guest]

Update on my last note - I am actually having problems with the duplicate
query on the accounts. It will only give me records if the account duplicated
within the same city. The design view shows criteria of >1 in one of the
fields created by the database for the count of times the account number
appears, which I assume is the search criteria for duplicates. If I take that

1 out it gives me records that duplicate across cities, but also unique

records that don't appear to be duplicates at all. As you can tell I'm fairly
new to this. I would just search through these in excel, but I have over 300k
records to look through.

 

[Roger Carlson]

Sorry, I completely mis-read the question.

If I understand correctly, you want to find all duplicate account numbers,
but ONLY when the City is NOT duplicated. If so, you can't do that in a
single query as far as I can see. I'd do this:

Create a standard Duplicates Query based on Account number. Call it
DuplicateAccounts. It might look like this:

SELECT Account, City
FROM AccountTable
WHERE Account In (SELECT [Account] FROM [AccountTable] As Tmp GROUP BY
[Account] HAVING Count(*)>1 )
ORDER BY Account;

Then create a second query based on the first. Make it a "Totals" query,
including only the two fields Account and City. Group on Account and City
and add a calculated Count(*) field and put <2 in the criteria of the Count
field. Something like this:

SELECT Account, City
FROM DuplicateAccounts
GROUP BY Account, City
HAVING Count(City)<2
ORDER BY City;

You'll have to modify the above to match your actual table and field names.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L