asked on Conditional running totals in Microsoft Access
I have an Access table called 'Orders'.
One of the fields in this table is called 'Department'
There are 8 departments.
5 of the 8 departments are considered 'green'. And so any orders for these departments get a green tag placed on the box.
The other 3 are 'red'; any orders from these 3 departments get a red tag.
I know you can create a running total in Access, but how can I replace a Conditional running total?
In other words, let's say at the end of the week there were 25 green orders and 10 red orders.
At the bottom of the report I want to show two fields:
- Total Count of Green tags: 25
- Total Count of Red tags: 10
One of the fields in this table is called 'Department'
There are 8 departments.
5 of the 8 departments are considered 'green'. And so any orders for these departments get a green tag placed on the box.
The other 3 are 'red'; any orders from these 3 departments get a red tag.
I know you can create a running total in Access, but how can I replace a Conditional running total?
In other words, let's say at the end of the week there were 25 green orders and 10 red orders.
At the bottom of the report I want to show two fields:
- Total Count of Green tags: 25
- Total Count of Red tags: 10
Microsoft Access
Last Comment
PatHartman
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Forgot to mention ... in the database there is no field to keep track of the tag color.
I was thinking I could write something like this:
if ((DepartmentName = "HR") or (DepartmentName = "Accounting") or ( ...
then [update green tag subtotal]
else [update red tag subtotal]
But adding a lookup table where each department's tag color is maintained seems like the better way to do it.
Let me add this table, and then update my query to include this table. That should now give me the tag color for each order and then from there I will try both methods.
Thanks for your help.