Count Duplicates In New Column Same Table

[Keith13]

I want to count the amount of duplicates and have the corresponding number of duplicates in a column next to it. I need two different version for two different queries and I cant seem to figure it out. Below are two tables of what I need. Please any help would be amazing!!


This table is with one source and counts the number of ID's and puts the correct count in the Duplicate Count Column where it prints outs out the number of duplicates in every row where the duplicate exists. There is also other information in-between ID and Duplicate Count but these are the columns I need it to count the duplicates through the other information can be the same or different.

ID	Source	Duplicate Count
1	apple	2
1	apple	2
2	apple	3
2	apple	3
2	apple	3
3	apple	1
4	apple	1
5	apple	1

<tbody>
</tbody>

This Table is the same except has different sources that need to be separated

ID	Source	Duplicate Count
1	apple	2
1	apple	2
1	orange	3
3	apple	2
2	orange	1
3	apple	1
1	orange	3
1	orange	3
2	apple	1
3	apple	2
3	orange	1

<tbody>
</tbody>

 

[Joe4]

Welcome to the Board!

If you do an Aggregate (Totals) Query, Grouping on the ID and Source fields, and then doing a Count on any field, it will give you results like this:

ID	Source	Duplicate Count
1	apple	2
2	apple	3
3	apple	1
4	apple	1
5	apple	1

<tbody>
</tbody>

You can then link this query back to your original table on BOTH the ID and Source fields, if you want to show the duplicated rows like you have in your first example.

If you do not know how to perform an Aggregate (Totals) Query, see here: Access Tips: Calculating Totals in Queries

 

[Keith13]

Thank you I did get them to count. My other Question is how do I link it back to the original table?

I know all this can be done in a macro on excel but I want it all in access so I could just populate the data and when I needed a excel file I could make it and throw it out afterwards and not have to depend on a certain excel file to do the work. Just an FYI

 

[Joe4]

Just create a new query in Access. Add the Aggregate Query you just created and add the original Table.
Create joins between both the ID and Source fields between the two objects, and then select the fields you wish to display.

The following link shows you how to create joins, if you don't know how. You do not need to make it a left join. You can leave it as an inner join (the default option 1).
https://www.techonthenet.com/access/queries/joins2.php