Using query to count number of duplicated records and output a single value 1

[Viktoriah]

Hey guys,

I am new to access and I can't seem to figure out my problem. I am trying to create a column that will show the number of duplicates that I can carry to the end of the report later on.

I have a file that has many duplicates, I want to count the duplicates, I have been reading about dcount but I cannot get it to work.

what I want.

Model Number Duplicates
gk125 2
sk4 2
gk125
sk4
lls 1
I eventually want to delete the duplicates but keep the duplicate column that counted previously, but I can't even get past the easy stage of actually counting these duplicates.

I'm trying to use the following with dcount

Model number Duplicates
group by group by
=dcount([ROData].[Model Number],[ROData],>1)
I'm clearly doing something incorrect but I can't figure it out.

thanks for the help!

 

[Andrzejek]

I am trying to figure out your issue.
What I've guessed is:

You have a table with some data:

TableA
 Model Number
 gk     125
 sk       4
 gk     125
 sk       4
 lls      1

and you want to have the output of which Model/Number data appears more that once in your table:

Output:
 Model Number Duplicates
 gk     125      2
 sk       4      2

How far off am I?


---- Andy

There is a great need for a sarcasm font.

 

[Viktoriah]

yes correct. Sorry when I formatted it here it looked seperated but after submitting it did not leave spaces. The model number is combined I just want the first model number to report the number of duplicates throughout the database

So if there are 3000 gk125's than the first row with gk125 will say "3000"

 

[Andrzejek]

when I formatted it here it looked seperated but after submitting it did not leave spaces"
Always use Preview button to see how your post will look like. Use formatting buttons (above Preview and Submit Post) to format your post/replay.

"The model number is combined " that's even better So the ModelNumber is the name of one field in your table

Try this Select:
[tt]
SELECT ModelNumber,
COUNT(ModelNumber) AS NumOccurrences
FROM TableA
GROUP BY ModelNumber
HAVING ( COUNT(ModelNumber) > 1 )
ORDER BY COUNT(ModelNumber) DESC
[/tt]
Replace TableA with the name of your table.


---- Andy

There is a great need for a sarcasm font.