Duplicate Account Query

[Guest]

I asked this yesterday but the information I need to get back is a little
different. I have a large set of records with Cust Accts, mtns, transaction
dates, amounts, credit codes and CityState of my offices. I need to get a
list of all accounts that were touched by more than one of my city offices. I
am new to access so any specific details that can be given as to how to do
this query is much appreciated. The duplicate query does not work because it
returns all duplicates, including those within the same city only. I only
want to count accounts touched by multiple offices. I however can't just
delete those same city records, because potentially my "Baltimore" office may
have touched an account 5 times and my "New York" office may have touched it
three times. Does anyone have any suggestions on how to do this or if I can
even get Access to do this for me?

 

[Guest]

First off I highly recommend that you don't delete anything. It is likely a
big mistake. Rather put a Yes/No field in the table named something like
OldAccounts and update this field. Then use a query that excludes those old
accounts.

SELECT [Cust Accts], Count([CityState])
FROM (SELECT DISTINCT [Cust Accts], CityState
FROM YourTableName)
GROUP BY [CityState]
HAVING Count([CityState]) > 1 ;

If my mind is working this early in the morning, the SQL above should give
you a list of Cust Accts that have been touched by more than one CityState.
You need to plug in the correct table name for YourTableName.

 

[Guest]

Thanks for trying to help. I am not familiar with SQL but I did attempt to
create a query with the code you gave. When I try to run it, it gave me a
syntax error for the FROM statement. I enclosed the second CityState in
brackets and the name of my talbe in brackets. Now when I run it it is asking
for a parameter valued for CityState. I'm not sure how to proceed from there.

Is there anyway to do this actually using Access criteria rather than SQL?

Thanks,

Robert

First off I highly recommend that you don't delete anything. It is likely a
big mistake. Rather put a Yes/No field in the table named something like
OldAccounts and update this field. Then use a query that excludes those old
accounts.

SELECT [Cust Accts], Count([CityState])
FROM (SELECT DISTINCT [Cust Accts], CityState
FROM YourTableName)
GROUP BY [CityState]
HAVING Count([CityState]) > 1 ;

If my mind is working this early in the morning, the SQL above should give
you a list of Cust Accts that have been touched by more than one CityState.
You need to plug in the correct table name for YourTableName.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I asked this yesterday but the information I need to get back is a little
different. I have a large set of records with Cust Accts, mtns, transaction
dates, amounts, credit codes and CityState of my offices. I need to get a
list of all accounts that were touched by more than one of my city offices. I
am new to access so any specific details that can be given as to how to do
this query is much appreciated. The duplicate query does not work because it
returns all duplicates, including those within the same city only. I only
want to count accounts touched by multiple offices. I however can't just
delete those same city records, because potentially my "Baltimore" office may
have touched an account 5 times and my "New York" office may have touched it
three times. Does anyone have any suggestions on how to do this or if I can
even get Access to do this for me?

 

[Guest]

Please post the SQL of the query you tried. While in query design mode, go to
View, SQL view.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Thanks for trying to help. I am not familiar with SQL but I did attempt to
create a query with the code you gave. When I try to run it, it gave me a
syntax error for the FROM statement. I enclosed the second CityState in
brackets and the name of my talbe in brackets. Now when I run it it is asking
for a parameter valued for CityState. I'm not sure how to proceed from there.

Is there anyway to do this actually using Access criteria rather than SQL?

Thanks,

Robert

First off I highly recommend that you don't delete anything. It is likely a
big mistake. Rather put a Yes/No field in the table named something like
OldAccounts and update this field. Then use a query that excludes those old
accounts.

SELECT [Cust Accts], Count([CityState])
FROM (SELECT DISTINCT [Cust Accts], CityState
FROM YourTableName)
GROUP BY [CityState]
HAVING Count([CityState]) > 1 ;

If my mind is working this early in the morning, the SQL above should give
you a list of Cust Accts that have been touched by more than one CityState.
You need to plug in the correct table name for YourTableName.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I asked this yesterday but the information I need to get back is a little
different. I have a large set of records with Cust Accts, mtns, transaction
dates, amounts, credit codes and CityState of my offices. I need to get a
list of all accounts that were touched by more than one of my city offices. I
am new to access so any specific details that can be given as to how to do
this query is much appreciated. The duplicate query does not work because it
returns all duplicates, including those within the same city only. I only
want to count accounts touched by multiple offices. I however can't just
delete those same city records, because potentially my "Baltimore" office may
have touched an account 5 times and my "New York" office may have touched it
three times. Does anyone have any suggestions on how to do this or if I can
even get Access to do this for me?