Pete Grieves
asked on
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
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
John Tsioumpris
Without the query...without sample data.......i think it would be easier to make the report in Access...
ASKER
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.
The excel sheet I just attached is the current data. The query was attached to the original post.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
If so you can use group selection to hide the UPC_CO groups that have only 1 SKU. Will that work?
mlmcc
ASKER
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.
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?
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks everyone!
You're welcome.