Programming an Inventory Bin Card

[silbon]

I use MS-Access 2010 in Windows-7 and I want to program a Bin Card which has the following fields (just as in a Bank Statement):
1. On the first line will be 'Opening Balance'
2. On the next line is: "Qty_Received". ...."Qty_issued "....."Bala nce"
3.More entries are added of quantities received and issued during the month.
My problem is how to program an accurate running balance.

Would be most grateful for any help. Thanks.

[NeoPa]

Running balances (or Running Sums) are handled in Reports. They are not handled in queries.

I suggest you design your query with the basic information in it per record, then use it in a report where one or more controls have the Running Sum property set to Yes.

[silbon]

I am aware that running sum property is available in reports. But this still does not solve my problem which starts with an opening balance and on the next line it is included to arrive at a "line balance" comprising of opening balance + Received - Issued. On the third line and so forth it is the previous line balance + Received - Issued.
If you could help me to translate this into MS-Access code, it would be much appreciated. I am sending an attachment of the Bin Card. Thanks for your help.

[NeoPa]

I can't help much ATM as I have no understanding of what the opening balance pertains to or how it is stored. I have very little appreciation also, of the objects you're managing. Hence, the best I can do without a better explanation of what you're working with, is to introduce you to the concepts which it may help you to explore. Had I known from your question, that you already understood about the Running Sum property, then I wouldn't have introduced it of course.

Working from details on a web page is necessarily going to be working in the dark to some extent. How much info there is to work with depends on the question. Currently there is too litle information to look at providing answers.

[silbon]

As displayed in the BINCARD.PDF sent yesterday, this is a parent and child relationship in MS-Access 2010. The fields in the upper section(or Main Form) are from the the header or parent file which includes the opening Inventory balance for an item. Various transaction entries for this item are displayed in the lower section(sub-form/child file). The two files are linked by a relationship(on e to many) indexed on Document Number. Now since the opening balance is displayed from the header file this figure has to be taken into account when displaying the line balance in the first transaction entry in the sub-form. For all subsequent transaction entries the figure in the previous line balance is taken into account. The method is the same as a Bank Account Statement. I need to know how to program this in MS-ACCESS. Thanks.

[zmbd]

Silbon,
And now we get into the crux of the matter... please show your work and maybe tell us all of the details of your DB?

You start out asking about what appears to be a simple running balance.
Then you tweak it with the balance forward.
Then you post a PDF... no real information or details.
Then you finally give us some more information... tweaking the question yet again.

One solution.
Running sum.
Two unbounds
One calcs the running total in the detail as one would expect
One subs the running total from the BF in the parent
Do conditional check in the second if needed
You can then store the final calc in the table at close.

You could do this in just one field; however, I like to see the ganular level for trouble shooting and it's easy enough to hide the control you don't want seen.

Now, while were at it, please understand Bytes is not a code writing or homework service... instead, we're a group of unpaid volunteers that enjoy helping people thru the rough spots in their projects.

[NeoPa]

Z has made most of the points I would have Silbon. I would just like to emphasize, for any future questions you may have here, that what you came up with after three posts and some prompting is the bare minimum that should have been in the first post. It could be laid out more clearly but it does at least have all the pertinent information. You need to understand that we aren't here to spend time eliciting from you all the details that you, as an intelligent adult working in databases, should know are needed.

As for your question, it seems that Running Sum isn't even required here (If I read the PDF correctly). You would need a Header and Footer for the Part. In the Header you would show the raw value from the Part table (The Brought Forward Balance). In the Detail you would simply show the value for each transaction. No Running Sum. In the Footer you would have two controls :

1. To sum the values from the Transactions. Use a formula =Sum([TranValue].
2. To show the new total. Use a formula =[B/F] + [TranSum] (Where [B/F] comes from the Part table and [TranSum] is the field from point #1).