Cumulative Total – Multiple Products
11 May, 2015
Hi There:
I have a little database with purchase transactions for a number of different products. The database columns I am using are Date/Time, Product ID, Quantity.
Every time inventory is purchased, an entry is made in this table with the quantity being, say, -1, if one item is purchased.
I want to do a running total for a given date range (eg. Current Month or Last Month) to see what day (and time of day) things are bought and how much (overall) we are going through things.
For example, if I have transactions for Water and Orange Juice as follows:
Date Product Quantity
March 12@7:30am Water -1
March 12@7:45am OJ -1
March 13@7:30am Water -1
March 15@8:30Am Water -1
March 15@9:30am OJ -1
March 16@7:30am Water -1
March 17@7:30am OJ -1
March 18@7:30am Water -1
I have created a calculated field to multiply these by negative 1 to get our positive value.
In my report I have the Date, Product, and Quantity elements. I used the 'Accumulated Total' function on the Quantity.
The challenge is that I want an accumulated total for each product, not all products aggregated together. I get the following back:
March 12@7:30am Water 1
March 12@7:45am OJ 2
March 13@7:30am Water 3
March 15@8:30Am Water 4
March 15@9:30am OJ 5
March 16@7:30am Water 6
March 17@7:30am OJ 7
March 18@7:30am Water 8
What I would like to get back is the following:
March 12@7:30am Water 1
March 12@7:45am OJ 1
March 13@7:30am Water 2
March 15@8:30Am Water 3
March 15@9:30am OJ 2
March 16@7:30am Water 4
March 17@7:30am OJ 3
March 18@7:30am Water 5
I have a little database with purchase transactions for a number of different products. The database columns I am using are Date/Time, Product ID, Quantity.
Every time inventory is purchased, an entry is made in this table with the quantity being, say, -1, if one item is purchased.
I want to do a running total for a given date range (eg. Current Month or Last Month) to see what day (and time of day) things are bought and how much (overall) we are going through things.
For example, if I have transactions for Water and Orange Juice as follows:
Date Product Quantity
March 12@7:30am Water -1
March 12@7:45am OJ -1
March 13@7:30am Water -1
March 15@8:30Am Water -1
March 15@9:30am OJ -1
March 16@7:30am Water -1
March 17@7:30am OJ -1
March 18@7:30am Water -1
I have created a calculated field to multiply these by negative 1 to get our positive value.
In my report I have the Date, Product, and Quantity elements. I used the 'Accumulated Total' function on the Quantity.
The challenge is that I want an accumulated total for each product, not all products aggregated together. I get the following back:
March 12@7:30am Water 1
March 12@7:45am OJ 2
March 13@7:30am Water 3
March 15@8:30Am Water 4
March 15@9:30am OJ 5
March 16@7:30am Water 6
March 17@7:30am OJ 7
March 18@7:30am Water 8
What I would like to get back is the following:
March 12@7:30am Water 1
March 12@7:45am OJ 1
March 13@7:30am Water 2
March 15@8:30Am Water 3
March 15@9:30am OJ 2
March 16@7:30am Water 4
March 17@7:30am OJ 3
March 18@7:30am Water 5
Hi,
what I would do is to have a master query and an append sub-query, use the same 3 columns in each, the only difference between the master and sub queries is that they each would have a Product filter, and in the master query the Product filter would be set to Water whereas the sub-query Product filter would be set to OJ.
I hope that makes sense, if there are any further queries then please let us know.
regards,
Dave
what I would do is to have a master query and an append sub-query, use the same 3 columns in each, the only difference between the master and sub queries is that they each would have a Product filter, and in the master query the Product filter would be set to Water whereas the sub-query Product filter would be set to OJ.
I hope that makes sense, if there are any further queries then please let us know.
regards,
Dave