Are running totals in a form/table possible?

[newmusicmark]

Hi,
I'm trying to get a running total in a subform or table, is this possible? I'm using Access 2002. What I have is a subform that lists all the fabric a vendor has ordered under a contract from our company. For example, the contract was set up with an order of 10,000 yrds of fabric. Each time they place an order, a PO is created and that amount of fabric used in that PO is subtracted from their total and we have the remaining balance. I know I can put a current balance for their contract by summing all of the fabric they have bought in all of their POs, but I want to have what their balance was each time they placed an order.

Big Jim's Clothing Farm

PO Amount Balance
1 10 110
2 10 100
3 10 90
etc... until balance is zero

I know how to write this in Excel but can't figure it out in Access. Any help would be great!

Also, is it crazy to type in the first balance (110 in this example) and have Access auto-fill the remaining ones or am I thinking too Excel-like? Probably would be easier to have a starting balance, say 120 and then have it subtract each amount, huh?

Thank you,
Mark

 

[Russell Hauf]

It is possible in some cases. Can you tell me if the PO numbers will be in ascending order (as opposed to random)?

Also, how & where are the yards of fabric stored, and same question for the PO's? I'm assuming they are in different tables...

Thank you,

Russell

 

[newmusicmark]

Hi, Thanks in advance for your help. I thought I had posted a reply yesterday but I guess I didn't save it!

Anyway, the PO numbers will not be in ascending order. They will be random, although sometimes ascending.

POs are in a PO table. They are linked to the Contract table as there are many POs for one contract. The Contract table has the initial purchase amount. Yards of fabric purched are in the PO table. Each PO has a amount of fabric purchased.

 

[Russell Hauf]

Ok - is the initial amount of fabric purchased in a table other than the PO table (the 10,000 yards)?

Is there a PO date? How will you be ordering the POs?

 

[newmusicmark]

PO Numbers will be random. They will be in order of the day of the PO, but there sometimes be more than one PO for a date.

Yards of fabric are specific to each PO so they are stored in the PO table. POs are a separate table and are reference from the Contract table. One contract will give many POs.

 

[Russell Hauf]

Ok, so in your example a customer orders 10,000 yards of fabric, to be bought in small amounts. Where is the 10,000 stored? Hopefully in the contract table...

What you can try is to create a query, with both the PO table and the Contract Table (connected by Vendor, hopefully). Add PO Date, PO#, Yards Bought, Vendor, and Total Yards Ordered (from the contract table). Do you want to list all vendors? I guess it doesn't matter - if you don't want to, then put one vendor in your criteria for the vendor field.

Anyway, sort ascending by Vendor, ascending by PO Date, and ascending by PO# (I know that these will be random, but we will need to differentiate between PO's that are on the same date).

Then add a field in your query something like this:

RunningTotal: [Total Yards Bought] - DSUM("[Yds Bought]","[PO Table]","[Vendor] = '" & [Vendor] & "' AND [PO Date] <= #" & [PO Date] & "# AND [PO Number] <= " & [PO Number])

If your PO Number field is character and not numeric, then it would be something like this:

RunningTotal: [Total Yards Bought] - DSUM("[Yds Bought]","[PO Table]","[Vendor] = '" & [Vendor] & "' AND [PO Date] <= #" & [PO Date] & "# AND [PO Number] <= '" & [PO Number] & "'")

HTH,

Russell

 

[newmusicmark]

Ah... Thanks. That got me on the right track. Since the POs may be out of order, I just added a PO_ID field that would number them the way they were added and sorted by that.

I have to admit though that I'm still pretty new at writing these types of complex criteria. All those #, ', & can get pretty confusing too me so I'm not sure I understand 100% what this is doing. But, I got it to work so thanks!

If you know of any book where I could learn more about DSUM and writing more complex equations like this, I would appreciate it.

Thanks again.
Mark

 

[Russell Hauf]

I'm not sure on the best book for SQL - the ones I've read haven't been that great. But DSUM and the other database functions are well documented in Help - take a look!

-Russell