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

How to Deduct from a different table based on batches

P: 1
I have products table and another table that keeps productID plus batches of different purchases of same products
Problem is how do I deducts whenever there's sales order form from products table
The products table

While the batch table is
My big problem is multiple Product can be Ordered at the same time
Thanks for your quick response
Jan 15 '17 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 5K+
P: 5,397
How much experience do you have with database design?

You do not indicate which fields in your tables are the primary keys. Of course we can guess; however, much easier to know.

Simplest solution may be an Update query against the two tables using a calculated field. Having the primary key fields would be most helpful.

You also do not indicate if you are using a Form (yes, please) or interacting directly (not best practice) with the database.

Finally, it's not normally best practice to store a calculated value in the database; however, in your case, I don't know enough about your schema to determine if an Aggregate query would be a good fit - normally, I would design a database with a running ledger tracking purchase and sales from "cradle to grave" for your inventory and either a running totals type query or an aggregate query should return the stock on-hand.

If you haven't already, you might take a look at
Database Normalization and Table Structures
Jan 20 '17 #2

Post your reply

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