3 tables, 1 calculated value

[Draggonn]

Hello all. I'm having a bit of a problem. I'm using Ms Access 2003.
I have 3 tables. Incoming Products, Outgoing Products, and Products In Stocks.
Lets say I have 25 of product A in the Products In Stock table. If I add in the table of the incoming products that I have 10 new incoming product A, how can I make it so that it adds +10 to product A in the Products In Stocks table?

[patjones]

Hello all. I'm having a bit of a problem. I'm using Ms Access 2003.
I have 3 tables. Incoming Products, Outgoing Products, and Products In Stocks.
Lets say I have 25 of product A in the Products In Stock table. If I add in the table of the incoming products that I have 10 new incoming product A, how can I make it so that it adds +10 to product A in the Products In Stocks table?

How are you interacting with your tables? (i.e. forms with text boxes, etc.) If you're doing this in Visual Basic it shouldn't be too bad. Let me know a few more specifics and hopefully we can attack this problem...

Pat

[Draggonn]

How are you interacting with your tables? (i.e. forms with text boxes, etc.) If you're doing this in Visual Basic it shouldn't be too bad. Let me know a few more specifics and hopefully we can attack this problem...

Pat

yes, i am using forms to do them. i don't know vb at all, which makes things really hard for me, however i do know sql.
here are my tables:

Table Name =  Products In Stock
Field ; Type ; IndexInfo
codep ; text ; primary key
namep ; text
quantstock ; number
price ; currency
namem ; text ; reference key for manufacturer

Table Name =  Incoming Products (Bought products)
Field ; Type ; IndexInfo
codec ; text ; primary key with codep
codep ; text ; primary key with codec
quantity ; number
(i designed it in a way that there can be many lines with the same product, since a product can be brought it several times)

Table Name =  Outgoing Products (Sold products)
Field ; Type ; IndexInfo
codec ; text ; primary key with codep
codep ; text ; primary key with codec
quantity ; number
(here is the same as the second table)

what i had in mind was that when the user opens up a certain product in the form for Products In Stock, the forms then searches for Incoming and Outgoing products A and then subtract both their quantities from each other. Thus the user doesn't have to touch the "quantstock ", so I was thinking i should probably remove it.
what i can't figure out is how to make the form look automatically for the same product in the other 2 tables.

Thanks for trying to help ^__^

[patjones]

Might it be possible to use the SUM function in SQL? In other words, to get the sum of all incoming products with a certain "codep" you could try something like:

 
SELECT SUM(quantity) FROM [Incoming Products] WHERE codep= "product A"

And for the outgoing products:

 
SELECT SUM(quantity) FROM [OutgoingProducts] WHERE codep= "product A"

Then you could subtract outgoing from incoming, and you would have the result that needed to be added to what you have in stock and displayed on the form. If I'm not mistaken, you can use the Control Source property of a text box to assign it just such a calculated value...

Pat