running balances (Access 2000 SP3)

Topic

New Reply

I’m trying to get a running outstanding balance by item in a report where the item is repeated several times due to the fact that it’s in more than one product order. This is how we’d
like it:

Order item qty on hand short
12345 101 6 100 0
12345 102 9 27 0

12346 101 6 98 0
12346 102 25 18 -7

I can’t figure out how to do this and have searched in vain for an answer. A running total isn’t doing it.

Reply | Quote

Replies

Do you want a running balance by item? Is there a date field to keep the transactions in order or is it a ID (autonumber field)?
The third line on your example should probably 94 as the on hand figure.
To get the on hand figure I would use a DSum based upon the date or ID field to get this figure for each line. This will take a while to run this report if there are a lot of items for the report.

Reply | Quote

yes, I know that the third line should have been 94, but right in the middle of my typing I got called away and then when I got back I just went typing away like I knew what I was doing….

What we’re trying to do is show, on the report, that the inventory is going down for the item as the orders are displayed. I’m trying to use DSUM, and I would consider the item to be the key field, but try as I might, I can create a running total, but not a running calculation. I’ll keep trying but if anyone has anything else to suggest, I’d be eternally grateful.

Reply | Quote

What I had alluded to in my earlier post was to use an ID field (or for that matter a date/time field) as well as the item field. You see, it’s not sufficient to just use the item field, as you will always get the same answer.
Can you let us know the structure of the table where these transactions are.

Reply | Quote

I looked at the underlying query and table for this report and there is no key field. We have multiple dates, multiple order numbers, multiple customer numbers, multiple items, etc. So I guess DSUM() won’t work here. I’m thinking of tying in a subreport, but that’s about all I can do here, I guess. Unless I add a field to the underlying table that calculates this via an update query ….

Thanks, Patt! I’ll let everyone know how it goes.

Reply | Quote

Why don’t you introduce an autonumber field in this table, that field can then be used for the DSUM.

Reply | Quote

Why don’t you introduce an autonumber field in this table, that field can then be used for the DSUM.

Reply | Quote

What I had alluded to in my earlier post was to use an ID field (or for that matter a date/time field) as well as the item field. You see, it’s not sufficient to just use the item field, as you will always get the same answer.
Can you let us know the structure of the table where these transactions are.

Reply | Quote

yes, I know that the third line should have been 94, but right in the middle of my typing I got called away and then when I got back I just went typing away like I knew what I was doing….

What we’re trying to do is show, on the report, that the inventory is going down for the item as the orders are displayed. I’m trying to use DSUM, and I would consider the item to be the key field, but try as I might, I can create a running total, but not a running calculation. I’ll keep trying but if anyone has anything else to suggest, I’d be eternally grateful.

Reply | Quote

Do you want a running balance by item? Is there a date field to keep the transactions in order or is it a ID (autonumber field)?
The third line on your example should probably 94 as the on hand figure.
To get the on hand figure I would use a DSum based upon the date or ID field to get this figure for each line. This will take a while to run this report if there are a lot of items for the report.

Reply | Quote