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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,129
Office Version
  1. 365
Platform
  1. Windows
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.
 

OSUmike03

New Member
Joined
Jun 20, 2007
Messages
20
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).
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,129
Office Version
  1. 365
Platform
  1. Windows
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).
 

OSUmike03

New Member
Joined
Jun 20, 2007
Messages
20
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,129
Office Version
  1. 365
Platform
  1. Windows
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).
 

OSUmike03

New Member
Joined
Jun 20, 2007
Messages
20
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?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,129
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,181,647
Messages
5,931,207
Members
436,783
Latest member
darkwingduck1

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
Top