I wasn't sure how to title this, I apologies if it doesn't make sense.
I have a database I've created with the data out of our main system. The data lists each customer charge as a separate entry, but with the same transaction number. So Joe has 2 entries with transaction number 205, one with charge ch497, and one with ch520.
I am trying to get a count of how many customers required direct service. So if ch497 requires service and ch520 may require service, I want to count only ch497. (I have created a list of which charges require service, may require, or is a non-service, and have that classification pulled into a table with my beginning data.) I have also created a column in my table labeled "IncludeInCount" to denote a "yes" or "no". My plan it to create a query in the end to count only the "yes".
Is there a way I can get access to auto populate the "IncludeInCount" based on the denotation in the "Service" column? My table has 98442 lines, and that's only half of year. Any help in reducing the manual process is greatly appreciated. This is my first time in many years to be using Access again, I am very rusty.
Here is some sample data showing each of my possible situations with the "IncludeInCount" column completed with the answer I would like to have calculate.
Customer Transaction# date ChargeCode qty Amount Service IncludeInCount Joe 205 2/15/2021 ch497 1 50.00 service YES Joe 205 2/15/2021 ch520 1 10.00 may NO Smith 206 2/15/2021 ch497 1 50.00 service YES Smith 206 2/15/2021 ch352 1 75.00 service NO Fred 208 2/15/2021 ch750 1 10.00 may Yes Fred 208 2/15/2021 ch520 1 10.00 may NO
"Service" charges are always included, but included only once per transaction.
"May" charges are included if there is no "service" charge, and included only once per transaction.
"nonservice" charges are never included and have already been removed from data.