Show # of duplicates on report

[OSUmike03]

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.

 

[Joe4]

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]

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]

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]

That did it. Thanks.

 

[OSUmike03]

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]

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]

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]

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

 

[OSUmike03]

think I got it. thanks.