Update field from another table

[jhrs]

Hi, I have a problem with these tables in a inventory database:
I have table1 like this:

Id In Out date
100 23 15 01-10-2003
100 45 18 01-11-2003

ad so on, many id, and many dates and many IN and Out fields

what I want is to create a table where I put Stock = In + Out, it would be the first field, the next field is: stock= in + out + "the previous calculated field" based on the date, i hope I was clear

joining both table i would get something like this

id in out table2.stock date
100 23 15 8 01-10-2003
100 10 12 6 02-10-2003

thanks alot

 

[WayneRyan]

jhrs,

You don't want to store data like that, it will cause you
problems in the long run.

tblStock:
StockID - Autonumber
StockDescription - Text

tblStockReceived:
StockID - Foreign Key
InvoiceNumber - Number
ReceivedDate - Date
Quantity - Number

tblStockShipped:
StockID - Foreign Key
InvoiceNumber - Number
ShippedDate - Date
Quantity - Number

Then you can calculate at any time the balance on hand.

Wayne

 

[jhrs]

Hi, Wayne thanks for your attention, the problem is that I have the stockid already defined and is not autonumber, but can i update the table stock with the transactions of receiving and shipping on a determined day?