Counter that resets by field value

I have been asked to modify an existing Crystal Report so that it "only returns multiples". I recreated the report (less the totals line) as a 1st step query in MS Access 16 and am wondering how to go about the 2nd step.

There are two fields that I will ultimately group by; grp1 is 'UPC_Co' and grp2 is 'UPC_SKU', both can have multiple sub-entries, but I am looking to only display those records where UPC_Co has multiple UPC_SKU's under it.

My initial thought was to create a counter field that would populate and reset to 1 at each change of UPC_Co, but I'm not sure how to go about doing it. I tried to google this, but all the hits seemed to be for sequential counters for the entire dataset and that isn't what I'm after.

I DID see a couple of post about how to do it in VBA, but I know virtually nothing on VBA.  Anybody have any ideas on how to do this in SQL with Access?

Any help is greatly appreciated!  Query is attached for those interested
Pete GrievesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
Without the query...without sample data.......i think it would be easier to make the report in Access...
Pete GrievesAuthor Commented:
It is in Access. I told the end user I would export the final results to an Excel sheet. I just don't know how to supply all field data with sub-totals for the last 6 fields by UPC_Co where the UPC_SKU count is > than 1.

The excel sheet I just attached is the current data. The query was attached to the original post.
I think what is confusing us is your reference to Crystal Reports.  You no longer need to use Crystal reports?  You want an "Access" solution now?

This is a two tiered problem.
1. You have to find the the UPC's with multiple items.
2. Then you have to produce a recordset with only that data.

Depending on your SQL competence, you can do this one of two ways.
1. use a subquery
2. use nested queries.

The nested query is simplest since you can build it entirely by using the QBE.  The subquery solution will require you to switch to SQL View and write the SQL String manually.

1. Create a totals query that selects UPC_Co with a count.  under the count use criteria that selects count > 0 .  The SQL view will be something like

Select UPC_Co, Count(*) as RecCount
From yourtable
Group by UPC_CO
Having Count(*) > 1

2. Save this query with a meaningful name.
3. Create a new query and add the query you just created and the original table.  Join the two on UPC_Co and select whatever you want to see on the report.  Use this second query as the RecordSource for the report or export to Excel.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Is the Crystal Report grouped as desired?

If so you can use group selection to hide the UPC_CO groups that have only 1 SKU. Will that work?

Pete GrievesAuthor Commented:
I really regret even mentioning Crystal LOL

This is an access query written in SQL (attached to original post), the output is in an excel sheet attached to my first reply.

I need to count each instance of UPC_SKU that shares a given UPC_CO, then reset the counter for each UPC_SKU under the next UPC_CO, etc. Then use that count to output a second query where all SKU counts are >1 along with totals on 6 additional fields at the end of the record layout to an excel sheet as my output.
Did you understand my suggestion?
Pete GrievesAuthor Commented:
I did Pat, thank you,  that was a well stated suggestion, but The field I need to count isn't UPC_CO. Or,  perhaps I DIDN'T understand your suggestion.  

Let me clarify.  Let's say there are 1,000 records.  There are 53 unique UPC_CO numbers in that record set. I need the count for UPC_SKU, for each of the 53 UPC_CO.
Look again.  The query is not counting UPC_CO.  It is grouping by UPC_CO and counting the related records which is what you asked for.
Pete GrievesAuthor Commented:
Thanks everyone!
You're welcome.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.