Calculation Problems

[braveali]

Hello,

I am very new to MS Access (version 2010), I am creating an Stock Entry Database for my office stock.

I want to calculate 3 column's Data:



1. Total Item Purchased
2. Opening Balance
3. Stock So Far

Problem:
when I gave a calculated field in StockSoFar to calculate

[total_item_purchase] + [Opening Balance] = [Stock So Far]
5 + 5 = 10

so as soon as i enter the 1st record it shows it sums up 10, but actually it was the 1st entry so the total stock should be 5 and opening balance should be 0

can someone please guide me in it, that how should I input purchase item value, that automatically add in opening balance and stock so far, and opening balance on a new entry should be come from the previous sum values.

Please refer the attached pics + database (.zip) file for a quick reference.

Thanks

Ali

[R_Badger]

I dont run 2010 access but from your screenshots i guess your problem is that you are trying to do a calculation in a table, you may find it works by creating a query from the table. and setting up a field of the query as that calculation.

I hope that makes sense!

[RayMilhon]

I am going to make an assumption here and say you are trying to track inventory of consumable office supplies.

The issue with a calculated field in the table is the calculation is based on that record. Nothing else so As soon as you enter a quantity for total Items purchased the opening balance and stockssofar fields basically just copy that value. So you have 3 fields with the same value. Also your table is not normalized. That'll kill you as you build up your data. Do a google search on database normalization and post back when you have more questions.

[braveali]

Basically I want; when I add any new incoming office Stock (into Stock Entry Table in total stock purchase field), it will automatically add this value to previous Balance of that stock (i.e. if we buy 10 new office files, and we had 5 already in stock so the new available balance should be 15, and in the same way it also add 10 new files into total stock so far.

If i remove the data fields named as: OpeningBalance & StockSoFar from this Stock Entry Table, and create a separate table that show my stock status i still need to sum new stock in previous balance (and new stock should be add in StockSoFar field as well)

later on i will create an other table that will subtract the stock (when we issue it to any department/person)

please advice how could i do this?

Thanks

[antoncata]

Basically I want; when I add any new incoming office Stock (into Stock Entry Table in total stock purchase field), it will automatically add this value to previous Balance of that stock (i.e. if we buy 10 new office files, and we had 5 already in stock so the new available balance should be 15, and in the same way it also add 10 new files into total stock so far.

If i remove the data fields named as: OpeningBalance & StockSoFar from this Stock Entry Table, and create a separate table that show my stock status i still need to sum new stock in previous balance (and new stock should be add in StockSoFar field as well)

later on i will create an other table that will subtract the stock (when we issue it to any department/person)

please advice how could i do this?

Thanks

Primarily have to do a form of introduction.
Second you have to do a table containing a list of unique products.
Product name must be identical, in your example you basically have two products due to writing mistake ...
Then make a query based on that combo to filter the table and just do the resulting assembly.
But remember you have to remove from stock and you should always remove the old stock.

Thanks