Calculating a running total in a feild 1

[forwardslash]

I am new to the Access DB system and have been learning by doing. At this point I need some help.

How my DB works is that their is a ordered count, received count, and used count. What I need is to have a running total, which would be in the feild marked as units on hand.

I have the an inventory transaction part where I fill in the ordered count, received count, and used count but when it comes to the running total it will only give me the current feild count.

If there is anyone out there can help me it would be a big help...

Thanks,

Michele P

 

[jgoodman00]

Does:

UnitsOnHand = [ReceivedCount] - [UsedCount]

???

Is this the correct calculation?? James Goodman

http://www.james-goodman.co.uk

 

[forwardslash]

James,

Thanks for responding. It's hard to explain, but the DB has the top portion which is your basic parts information and then at the bottom it is a seperate for that allows me to do data entry.

What I do is I enter the date, part number, amount of product that we ordered, the amount of product we actually received, which gives me a calculation of amount of parts on hand. Then I enter an amount of parts used and this gives me an amount on hand.

What happens is when I enter a new entry on the next line it erases all of the data that was already entered, but if you click the line before it will give you the correct data. What I need to do is have an ongoing running total of parts on hand from everything that is entered.

I hope this helps you a little better and doesn't confuse you to much.

Thanks again,

Michele P
forwardslashdotcom@email.com

 

[jgoodman00]

In that case, you need a calculated control on your subform. This needs to be an unbound control, with a calculation set as its recordsource. It will need to use the sum function (twice I imagine). Something like as follows:

= (Sum[UnitsOnHand]) - (Sum[UnitsConsumed])


This should hopefully give the value you are interested in... James Goodman

http://www.james-goodman.co.uk

 

[forwardslash]

James,

I tried what you said and it does give me the right calulation, but when I Tab to the next line to add an entry it doesn't keep the total counts on hand - it becomes a blank feild. If I go back to the previous feild that was just entered it will show the calculation.

Hopefully you can help..

Thanks

Michele P

 

[jgoodman00]

What happens when you enter a value in this blank field? I think the calculation should then work.

If this is the case, you need to incorporate the Nz function:

Instead of this:
= (Sum[UnitsOnHand]) - (Sum[UnitsConsumed])

You need this:
= (Sum(Nz([UnitsOnHand],0)) - (Sum(Nz([UnitsConsumed],0))
James Goodman

http://www.james-goodman.co.uk

 

[forwardslash]

The calculation did work, but what is happening when I tab to the next entry line the calculations disappear on the top portion of the form . Is there a way to keep the totals running and show an accurate count?

Thanks,

Michele

 

[jgoodman00]

When you say 'tab to the next entry line', do you mean a new record (the new entry line)?

If this is the case & you are entering a new record, does the calculation go blank whilst you are entering the new record, but reappear when your new record is committed to the database, with the updated calculation results?? James Goodman

http://www.james-goodman.co.uk

 

[forwardslash]

Yes, it would be a new entry line. What it is doing is when you go to that line it only show that current lines info. It doesn't do the complete running totals, from previous entery's plue current ones. I would send you a print screen, but I don't think I can do it frm here.

I hope this gives you the info you needed.

Thanks

Michele P

 

[jgoodman00]

Email me a print screen. My email address is j.goodman00@btinternet.com

Why are you working on a sunday?? James Goodman

http://www.james-goodman.co.uk

 

[forwardslash]

I'm always working I'll send you that file in a little while.

Thanks,

Michele