Show # of duplicates on report

OSUmike03

New Member
Joined
Jun 20, 2007
Messages
20
Here is what I am trying to do.

On the query and report I am running, I have 1200+ records SORTED by a column that has some duplicates.

The reason behind these dupes is because these projects have more than one job order...therefore the project is listed 3 or 4 times, with a different job order on each one.

What I want to be able to do is have a function of some sort that shows up on the report that tells the user how many other duplicate projects are in the query/report.

For instance.

Let's say that this project; 7T-000-00 has 4 different job listings associated with it.

If you cycle through, and bring up one of these projects...somewhere on the report, it will show that there are 4 total job order listings associated with this project title.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
If you simply want to list the number of duplicates, try doing an Aggregate query where you Group On Project and Count the number of orders (see Aggregate Query in Access help for details/examples). You can also see an example of one of these Count queries I helped someone with here just the other day:
http://www.mrexcel.com/board2/viewtopic.php?t=283590&highlight=

You can then incorporate this query into your main query that you are using as the Control Source of your Report.
 
Upvote 0
I made a new query, and made just one field that had the FSR #(projects), then did the totals and grouped it by count...

...but now, when I entered it into the other query, instead of showing how many times the project is repeated...it says how many different projects there are (the column says 831 on every record).
 
Upvote 0
Take a little closer look at the link I provided.

You need to have TWO fields in your query.
One, the field you are grouping by (Project). In the Totals Row, leave this value as "Group By".
Two, add any other field from your table/query and change the Totals Row to "Count" under this field.

What this essentially does is group by each Project number, and return the count of records for each Project grouping. If you only have one field and have it set to Count, there is no grouping at all so it counts all records in that table/query (grand total).
 
Upvote 0
Actually...one small question/problem.

How do I directly link up both queries, seeing how this "new" query only has 500 records? All the projects are there...but they are just listed once.

I tried just adding it to the "old" query (added the new count column) but the records on the query/report sky-rocketed up to like...6 million
 
Upvote 0
You should be able to link your new "Count" query to the old one. If you link it correctly in a query on the "Project" field, it should not increase the number of records.

Then use this new query as the Control Source of your Report.

Note: If all the records in all your duplicates are always the same (at least the ones you want to show on your report), you may be able to do it all using just the new query (by adding the other fields with the "Group By" clause).
 
Upvote 0
You should be able to link your new "Count" query to the old one. If you link it correctly in a query on the "Project" field, it should not increase the number of records.

Then use this new query as the Control Source of your Report.

Note: If all the records in all your duplicates are always the same (at least the ones you want to show on your report), you may be able to do it all using just the new query (by adding the other fields with the "Group By" clause).

Here is how I am trying to link it. I go into my Project query, and go to design view. I go up towards the top where it shows relationships between the two tables that the query is currently using. I add the Count Query up there, and then in the next available column, I add the Projectcount column that was from the Count query.

When I run this, I get 600,000 entries. What am I doing wrong?
 
Upvote 0
Then you haven't joined or linked the tables at all. All you have done is listed both tables/queries, but not established any relationship between the two. So each and every record in one table/query is, by default, joined to each and every record in the other other, creating the massive dataset you are seeing.

You actually want to "join" the two queries on the Project field. To create the relationship/join/link, check out this topic in Access help:
Join multiple tables and queries in a query
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top