Duplicate Query

  • Thread starter Thread starter SeRene
  • Start date Start date
S

SeRene

Hi,

I have 2 different fields, CardSerialNum and Terminal. The
data in these two fields may appear more than once in the
table. I know that by using the Find Duplicates Wizard, i
can find out the number of duplicates. However, the number
of duplicates field will only appear if i select only ONE
field throughout the whole wizard process. If i select
another field in the 2nd step, the number of duplicates
field will not appear.

What i need is to show 3 fields in my results,
1st --> CardSerialNum
2nd --> NumofDuplicates
3rd --> Terminal

CardSerialNum need to show ONCE only, with the 2nd field
showing the num of duplicates for that particular
CardSerialNum. The last field should show the Terminals
which are "tied" to that particular CardSerialNum.
Occasionally, 1 CardSerialNum will have a couple of
different terminals tied to it. I have no idea how to
display that. Can someone help me on this??

Thanks a million!
 
Hi SeRene

if i'm understanding you correctly the following should meet your needs:

create a new query in design view with the fields that you want to see
outputted in the grid (ie CardSerialNum & Terminal), add CardSerialNum again

now click on the Total icon on the toolbar ('E') and then for the first
CardSerialNum choose group by, for Terminal choose Group By and for the
second CardSerialNum choose Count.
Run the query (!)

I think this should give you what you want.

Cheers
JulieD
 
if i'm understanding you correctly the following should
meet your needs:
create a new query in design view with the fields that you want to see
outputted in the grid (ie CardSerialNum & Terminal), add CardSerialNum again

now click on the Total icon on the toolbar ('E') and then for the first
CardSerialNum choose group by, for Terminal choose Group By and for the
second CardSerialNum choose Count.
Run the query (!)

I think this should give you what you want.

Cheers
JulieD
Click to expand...
Hi JulieD,

I've tried this method but the results aren't what i want.
The CardSerialNum appeared multiple times together with
the Terminal it is tied to. Is it possible to show a
CardSerialNum ONCE in that column? with the next field
showing the number of duplicates the CardSerialNum have?
while the last field will show ALL the different Terminals
attached to that particular CardSerialNum.
 
if i'm understanding you correctly the following should
meet your needs:
create a new query in design view with the fields that you want to see
outputted in the grid (ie CardSerialNum & Terminal), add CardSerialNum again

now click on the Total icon on the toolbar ('E') and then for the first
CardSerialNum choose group by, for Terminal choose Group By and for the
second CardSerialNum choose Count.
Run the query (!)

I think this should give you what you want.

Cheers
JulieD
Click to expand...
Hi JulieD,

I've tried this method but the results aren't what i want.
The CardSerialNum appeared multiple times together with
the Terminal it is tied to. Is it possible to show a
CardSerialNum ONCE in that column? with the next field
showing the number of duplicates the CardSerialNum have?
while the last field will show ALL the different Terminals
attached to that particular CardSerialNum.
 
Back
Top