"Cumulating" records for an inventory calculation

[Joachim]

Hi,

I'm about to rip my hair out finding the right approach
for a problem that must have been solved hundred times
over, with my Excel head screwed on, I can't get to see
the solution though:

I have inventory transaction data in a query like:

date Transaction
09/10/2003 100
09/11/2003 -10
09/12/2003 500

and with the appropriate query I need to calculate the
cumulative transactions, i.e., the daily total inventory
and make it available in a new query:

date Transaction Daily total inventory
09/10/2003 100 100
09/11/2003 -10 90
09/12/2003 500 590

In Excel you would just calculate the inventory position
per day (just the total for 09/12 would not be good
enough) by adding a column cumulating the transactions
over time, I haven't figured out Access to come up with
the same result.

I would not want to export into Excel, do it there and
come back, ideally there were some functions to do this in
a select query so that it is calculated "automatically"
versus some action query, I'll take any help to solve the
problem in step 1 though, sophistication can be step 2.

Any thoughts? Help is sincerely appreciated. I realize
that the answer is probably faster than me writing this
post, if you have your Access basics together and know
where to look. I'm surely not there yet.

Joachim

 

[Tom Ellison]

Dear Joachim:

You can calculate the running balance on a daily basis. If there are
multiple transactions on the same date, then the running balance will
reflect all transactions on that date simultaneously.

SELECT [date], [Transaction],
(SELECT SUM([Transaction]) FROM YourTable T2
WHERE T2.[date] <= T1.[date])
AS [Daily total inventory]
FROM YourTable T1
ORDER BY [date]

You must change YourTable above to the actual name of your table.

The T1 and T2 are aliases that allow you to specify separate
references to the same table. T1 is the main table you are passing
through in date order. T2 is another instance of that table used just
to sum the quantities through that date.

The technique is known as a "correlated subquery." The additional
SELECT statement:

(SELECT SUM([Transaction]) FROM YourTable T2
WHERE T2.[date] <= T1.[date])

is known as a subquery. The correlation is on the values of date.

Hi,

I'm about to rip my hair out finding the right approach
for a problem that must have been solved hundred times
over, with my Excel head screwed on, I can't get to see
the solution though:

I have inventory transaction data in a query like:

date Transaction
09/10/2003 100
09/11/2003 -10
09/12/2003 500

and with the appropriate query I need to calculate the
cumulative transactions, i.e., the daily total inventory
and make it available in a new query:

date Transaction Daily total inventory
09/10/2003 100 100
09/11/2003 -10 90
09/12/2003 500 590

In Excel you would just calculate the inventory position
per day (just the total for 09/12 would not be good
enough) by adding a column cumulating the transactions
over time, I haven't figured out Access to come up with
the same result.

I would not want to export into Excel, do it there and
come back, ideally there were some functions to do this in
a select query so that it is calculated "automatically"
versus some action query, I'll take any help to solve the
problem in step 1 though, sophistication can be step 2.

Any thoughts? Help is sincerely appreciated. I realize
that the answer is probably faster than me writing this
post, if you have your Access basics together and know
where to look. I'm surely not there yet.

Joachim

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts