Show all cumulative end of month quantites

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that stores all inventory transactions. Each record shows
either a positive or negative quantity to add or delete inventory of specific
part numbers / operations / locations by date and type of transaction. By
totaling all transactions we know how much inventory we have completed
through each operation. My problem is the boss now wants to compare
inventory balances by month. So, I need end of month inventories for every
month. I can’t just sum the transaction quantities by month because I need
all transactions <= the end of the month date, not just those completed
during the month. In other words I need the sum of all transactions
<=8/31/04, <=9/30/04, <=10/31/04, <=11/30/04 etc. Is there some way I can do
this without writing a separate query for each month, combining them into one
query and adding a query with the most recent month each month?
 
You could do this fairly easy using a running sum in a report.

1. Create a report into this table.

2. Open the Sorting'n'Grouping dialog (View menu).
In the upper pane of the dialog, select the date field.
In the lower pane, set these properties:
Group Footer Yes
Group On Month
This adds a group footer to the report.

3. In the date footer section, add a text box with these properties:
Control Source =Sum([Quantity])
Name txtMonthQuantity
Format General Number

4. Add another text box beside that one, and give it these properties:
Control Source =[txtMonthQuantity]
Running Sum Over All

The last text box should give you a progressive running quantity.
 
"By totaling all transactions we know how much inventory we have completed
through each operation."

I hope I am not being simplistic by suggesting that if you have a query that
totals in this manner,
that using this query as the basis of a crosstab query displaying by month
should suffice.



Allen Browne said:
You could do this fairly easy using a running sum in a report.

1. Create a report into this table.

2. Open the Sorting'n'Grouping dialog (View menu).
In the upper pane of the dialog, select the date field.
In the lower pane, set these properties:
Group Footer Yes
Group On Month
This adds a group footer to the report.

3. In the date footer section, add a text box with these properties:
Control Source =Sum([Quantity])
Name txtMonthQuantity
Format General Number

4. Add another text box beside that one, and give it these properties:
Control Source =[txtMonthQuantity]
Running Sum Over All

The last text box should give you a progressive running quantity.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

neenmarie said:
I have a table that stores all inventory transactions. Each record shows
either a positive or negative quantity to add or delete inventory of
specific
part numbers / operations / locations by date and type of transaction. By
totaling all transactions we know how much inventory we have completed
through each operation. My problem is the boss now wants to compare
inventory balances by month. So, I need end of month inventories for
every
month. I can't just sum the transaction quantities by month because I
need
all transactions <= the end of the month date, not just those completed
during the month. In other words I need the sum of all transactions
<=8/31/04, <=9/30/04, <=10/31/04, <=11/30/04 etc. Is there some way I can
do
this without writing a separate query for each month, combining them into
one
query and adding a query with the most recent month each month?
Click to expand...
Click to expand...
 
Thank you - I got this to work just as needed by using 'over group' instead
of 'over all'. Appreciate the help

Allen Browne said:
You could do this fairly easy using a running sum in a report.

1. Create a report into this table.

2. Open the Sorting'n'Grouping dialog (View menu).
In the upper pane of the dialog, select the date field.
In the lower pane, set these properties:
Group Footer Yes
Group On Month
This adds a group footer to the report.

3. In the date footer section, add a text box with these properties:
Control Source =Sum([Quantity])
Name txtMonthQuantity
Format General Number

4. Add another text box beside that one, and give it these properties:
Control Source =[txtMonthQuantity]
Running Sum Over All

The last text box should give you a progressive running quantity.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

neenmarie said:
I have a table that stores all inventory transactions. Each record shows
either a positive or negative quantity to add or delete inventory of
specific
part numbers / operations / locations by date and type of transaction. By
totaling all transactions we know how much inventory we have completed
through each operation. My problem is the boss now wants to compare
inventory balances by month. So, I need end of month inventories for
every
month. I can't just sum the transaction quantities by month because I
need
all transactions <= the end of the month date, not just those completed
during the month. In other words I need the sum of all transactions
<=8/31/04, <=9/30/04, <=10/31/04, <=11/30/04 etc. Is there some way I can
do
this without writing a separate query for each month, combining them into
one
query and adding a query with the most recent month each month?
Click to expand...
Click to expand...
 
Back
Top