Formula in query based on previous row?

[jive6243]

Hello all!

Quick question. WindowsXP, Access 2002. I have a table that shows sales and inventory levels for an item for each day of the week. Let's say the week starts out at 100 units. On Sunday, 7 units were sold, so the ending inv level for Sunday would be 93. Monday was 5, leaving ending inv at 88, etc.

Can I create a formula-based column in the table (or a query) that deducts the sales field from the previous row's ending inventory?

Thanks in advance for your reply!

 

[jive6243]

Thanks for your help - it's much appreciated.

 

[mresann]

Out of curiosity, what is the purpose of that particular procedure? It sounds like you are entering recalculated data from existing data. Are you creating a temporary table for printing or export? Otherwise you may be creating a potential data corruption problem.

On an inventory (or debit/credit) function which involves the addition or removal of stock, I would use a query that sums the quantity field as a whole, not references another datum that is based on a recalculation of the same data.

 

[jive6243]

mresann - thanks for your post. let me give you a bit of background. i currently do this in Excel. There's a tab for each day, which contains the items sold that day. All of the tabs roll up to a total tab. But it is has become too time consuming because items vary day to day, and i would have to manually update the totals page every day to account for new items. Check out the attached picture. I'm simply trying to duplicate that IF statement within an Access query or report.

I've been researching the DSum() function as per Pat's direction, but I have yet to make it work. Any suggestions would be appreciated.

 

[mresann]

OK, your original post said you were using Access, I assumed tables in Access. Of course Excel spreadsheets uses much more localized relative functions for use in a non-normalized database format. I use the same function procedure in a sports betting spreadsheet I created in excel.

You can do this with one table, but you will probably have to 'normalize' the structure with at least two tables. One table will hold your items, yoru other table will contain the actual orders, then using queries to show the results similar to your excel tables.

 

[jive6243]

I will try that route - seems easier than writing the crazy functions. Thanks!

 

[d_profesor]

It's been four years, then i have similiar but quite different case.

Let say I need to show Ending Inventory Level vs Day in access pivot table like this:

Ending Inventori Level
12/01/2009 x
12/02/2009 y
. .
. .
.etc

How should i do that, is for my case i need a query for running total inventori level (I think, yes), is there any route, more simple maybe, for that purpose?

Thx