Full Version: Inventory Road Block
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
Julio-Santiago
Hello,
I am new to Web DB. I have made great progress in the last 72 hours in navigating this new monster but in terms of positive outcomes, I am just scratching the surface...
I want to track inventory for styles that come in different colors and sizes.
I created one table for companies, one for styles and one table for colors.
I created a transaction table with a company field, transaction types (Sale, Lend, Consignment, Return, Production) and date
I created a transacion detail table with ID from the transaction table, style, color, size and Qty.
I created forms with subforms to enter information onto the tables.
I created 2 Queries one for Inventory Additions and one for Inventory Deductions by filtering transaction types.
Any help on how to proceed to show the Qty on Hand by Style, Color and Size?
If you'd so kind in explaining where to go as I don't understand "Code"
Thank you!!!
Julio
pbaldy
I'm not strong in web db's, but since nobody has answered I'll take a stab at it. Normally I'd say to use a totals query to sum up the transactions and calculate on hand from that. However, I don't think you can have a totals query in a web db. I think you're going to have to use Data Macros to keep a table updated. Normally that would be a products table, but it appears you don't have a single table. I think you might need one, with fields for style, color, size and Qty. Then your data macros would update the appropriate record based on each transaction. Querying that table would give you your quantity on hand by style, color and size.
FOr I could be all wet and there may be a much easier method.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.