Want Duplicate Information

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I ran a query on all accounts and selected unique values, which filtered out
the duplicate accounts. Now I want to see the duplicates. Any ideas on what
type of query to run to display the duplicates and nothing else?
 
Dear Ap:

Start with a aggregate query including a HAVING COUNT(*) > 1 and returning
the unique key to those accounts. Place this in an IN() clause for your
query.

The level of detial in my answer was constrained by the level of detial in
your post.

Tom Ellison
 
Thera is a create query wizard that might help you get started. In the top
menu bar, click on Insert, then Query. A dialog box will open. In A2K, the
fourth line in the list box is "Find Duplicates Query Wizard". Follow the
prompts... :)

HTH
 
Didn't think the other stuff would help, sorry. It is basically a list of
five thousand bank account numbers, that we expected to be unique. When it
was learned that there were duplicates, we wanted a list of just the
duplicates to investigate why two showed up on our list. The other stuff is
what you would expect in a list of bank accounts, the person's name, address,
account balance, date opened, etc...

Now, I took the table of 5000 records and with just the account number
selected, ran a query asking for unique values and the result went to 4925,
so there were 75 duplicates. I would like to run some sort of a query that
returns those 75 account numbers so we can run a report and have someone
follow up on them.

I must admit that your previous answer was above my level. I probably know
what an aggregate query is, but not by that name and I was lost with the rest
of it. I was sort of hoping there was a view dups or view unique boolean
switch somewhere. If my additional comments have given you the necessary
detail, perhaps you could tell me where to set the having count option, or
how the in() clause works. I could set an autonumber on the table, however,
the only unique key right now is the account number itself, which we now know
is not unique. Let me know if you need any other information. Thanks.
 
Dear Ap:

Well, I could craft a query for this:

SELECT AccountNumber, COUNT(*) AS Ct
FROM YourTable
GROUP BY AccountNumber
HAVING COUNT(*) > 1

Here I've guessed at table and column names. Fix it up with your actual
names.

There will not necessarily be just 2 of each AccountNumber and 75 pairs of
those. An account number could be trippled, or more, feasibly.

Tom Ellison
 
Back
Top