Add result from previous record

[Jenn Englehardt]

I am trying to set up a query that will pull the last record's result and add it to the current record's calculation

Example

2003/08 Inventory Balance = 15,00
2003/09 Receipts = 10,00
2003/09 Shipments = 12,00
2003/09 Inventory Balance = 2003/08 Inventory Balance + 2003/09 Receipts - 2003/09 Shipment
(15,000+10,000-12,000 = 13,000

Date is in yyyy/mm format

If you need more information, please let me know

Thanks
Jenn

 

[Tom Ellison]

Dear Jenn:

This is a classic instance for the use of a correlated subquery. For
your case, the subquery locates the row with the previous "record" by
filtering to all the previous records and choosing the one with the
maximum date. You can then reference the row with that date and
obtain the quantity value.

If you were to provide specific information on how to query this data,
I could probably add the code for this. Please write a query that
return the Date column, the Quantity column, and shows things like
Inventory Balance, Receipts, and Shipments.

Please consider that such a value should not be stored in the table,
but derived from the recorded data when needed.

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