Update Query

[pancho13]

update query question - MS ACCESS

update query question - MS ACCESS
Hello there, Gurus of the world

I'm playing with an Inventory db and I need to update the "Qty on hand" field according with the transaction that may occur from another table.

I've got 2 Quick and simple questions.

1. How could I update a field in table "A" by adding or taken away from a field in table "B"?

2. How could I update by adding or taking away from an another table, using a parameter.......
specifically...
if I've got a field that contains specific data... how can I use that field to update the field in table "A" by adding or subtracting accordingly from table "B"???


Any suggestions

Cheers from Down Under!!

 

[neileg]

No you don't.

You should not store the amount on hand anywhere. You should calculate the balance whenever you need it .

Search in this forum. Inventory and stock control are regulars.

 

[pancho13]

No you don't.

You should not store the amount on hand anywhere. You should calculate the balance whenever you need it .

Search in this forum. Inventory and stock control are regulars.

.

OOPPSS!!....any suggestion how can I do this calculation after the event on any transaction??

btw....why shouldn't calculate the balance at the time of the transaction????....I do want to know my stock balance in the event of any transaction!!! any special reason for it?


Cheers from down under

 

[neileg]

You should be holding the transactions that affect the stock balance, goods inwards, stores issues or sales, etc. These might be in a single table, or might be in a series of tables. Sum these transactions to get the balance.

Lots of reasons why this approach is the right one. Here's a few
- You can determine the balance at any time, not just the current balance, and you have a full audit trail of how the balance is arrived at
- If you hold the balance in a table, you will have to recalculate the balance after every transaction. Why should you need to?
- What happens if the routine to calculate the balance is run twice, or not at all? How will you know your balance is wrong?

As I say, both the theory and practice of inventory systems is often discussed in here. Search the archives.