query to copy record by quantity into an archive table ...

[OrionTC]

Hi all, i was wondering how it was possible to create a query where by when you update the quantity field it will copy the "old" entire record into another table and keep the existing one updated with the new quantity.

for example:

Tape Range: 20 new media
Tape Type: DVD
Date Added: 20/11/2010
Quantity: 20

When the quantity field is changed to say 15 i want it to make a record that 20 previously existed and 5 have now been used on "todays date" acting as a mini audit trial.

is this possible using a query?

 

[jdraw]

There is a sample data base that claims to be a simple Audit Trail at this site.

http://www.utteraccess.com/forum/Audit-Trail-Database-S-t1576962.html&pid=1576962

It may give you some ideas as to how to create an audit record/log for your transactions.

 

[DCrake]

Basically you need a transaction table that records goods in goods out such as quantities, dates, prices, etc. Then depending on what stock control method you are using (LIFO, FIFO,AVCO,ETC) keep a balance of hat you have in stock.