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

How to Deduct from a different table based on batches

P: 1
Hi,
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
ProductID
CategoryID
Description
Quantity
CostPrice
SalesPrice

While the batch table is
ProductID
QtySold
BatxhNo
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


zmbd
Expert Mod 5K+
P: 5,287
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.