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?
 
PatHartmanCommented:
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.
0
 
John TsioumprisSoftware & Systems EngineerCommented:
Without the query...without sample data.......i think it would be easier to make the report in Access...
0
 
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mlmccCommented:
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?

mlmcc
0
 
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.
0
 
PatHartmanCommented:
Did you understand my suggestion?
0
 
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.
0
 
PatHartmanCommented:
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.
0
 
Pete GrievesAuthor Commented:
Thanks everyone!
0
 
PatHartmanCommented:
You're welcome.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.