help with updating the records

[rythem]

I have the following query:

SELECT Inventory.[Product Number], Inventory.[Make/Model], Inventory.[Stock Quantity], Requisition.Quantity, [Update Inventory].[New Quantity], [Check Stock].[Avaliable Quantity], [Check Stock]![Avaliable Quantity]+[Update Inventory]![New Quantity] AS [Net Quantity]
FROM ((Inventory INNER JOIN [Check Stock] ON Inventory.[Product Number]=[Check Stock].[Product Number]) INNER JOIN Requisition ON Inventory.[Product Number]=Requisition.[Product Number]) INNER JOIN [Update Inventory] ON Inventory.[Product Number]=[Update Inventory].[Product Number];



My problem is as follows:
I have an invenotry table, and i have an inventory update table used to add any new itmes that come in. i also have a requisition table.

I want to be able to implement the following formula:
initial stock quantity+new incoming quantity (for the day) - requisitions (for the day)

But with this query ever time it runs, the initial quantity is taken from the one that i set it on the first day. I made new incoming additions and some requisitions after that.

So can i somehow get the above formula to do this:
result 1 =initial stock quantity+new incoming quantity - requisitions
next time after updates
result2 = result 1 +new incoming quantity (for the day) - requisitions(for the day)


thankyou very much

 

[neileg]

There's not enough info to understand exactly your structure. Here's my view of how an inventory system would work.

I would have a product table linked to a transaction table. The transactions would be of the following types:
Initial stock (+ve qty)
Goods received from suppliers (+ve qty)
Goods returned to suppliers (-ve qty)
Goods issued to users (-ve qty)
Goods returned from users (+ve qty)
Adjustments (-ve or +ve qty)

The balance is then computed by summing the transactions.

The same logic would be followed if the transactions are held in different tables, but you need to summ all of the ins and outs, not just the ones for today.

 

[rythem]

Query

So do u think that i should just have one table and enter +ve and _ve values in it. Then sum it up to display on the report.

Sory about that, but my structure is something like this.
I have an inventory table where i enter any item that comes in specially that does not exist in the database. Next time i get a new shipment of an items that already has an entry in my database, i enter that quantity through the Update inventory table. Then i have the requisition table, used to remove anything from the stock room.

So the final quantity on the report should be initial + any new incoming - requisitions.
my problem is that my initial quantity is static even though it was the very first qyuantity that i had entered.

Hope you can help me with this .

Thanks