By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,497 Members | 2,113 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,497 IT Pros & Developers. It's quick & easy.

3 tables, 1 calculated value

P: 10
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?
May 13 '08 #1
Share this Question
Share on Google+
3 Replies


patjones
Expert 100+
P: 931
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
May 13 '08 #2

P: 10
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:
Expand|Select|Wrap|Line Numbers
  1.  
  2. Table Name =  Products In Stock
  3. Field ; Type ; IndexInfo
  4. codep ; text ; primary key
  5. namep ; text
  6. quantstock ; number
  7. price ; currency
  8. namem ; text ; reference key for manufacturer
  9.  
  10. Table Name =  Incoming Products (Bought products)
  11. Field ; Type ; IndexInfo
  12. codec ; text ; primary key with codep
  13. codep ; text ; primary key with codec
  14. quantity ; number
  15. (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)
  16.  
  17. Table Name =  Outgoing Products (Sold products)
  18. Field ; Type ; IndexInfo
  19. codec ; text ; primary key with codep
  20. codep ; text ; primary key with codec
  21. quantity ; number
  22. (here is the same as the second table)
  23.  
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 ^__^
May 14 '08 #3

patjones
Expert 100+
P: 931
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT SUM(quantity) FROM [Incoming Products] WHERE codep= "product A"
  3.  
And for the outgoing products:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT SUM(quantity) FROM [OutgoingProducts] WHERE codep= "product A"
  3.  
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
May 14 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.