Confusing

[ChrisGow]

Ok this may seem very confusing as alot of things need to happen for what I am trying to do

I am doing an inventory database that contains all order, client, purchasing, and inventory information.

I have a table that has all of the different products we sell (also includes which of the following tables are needed) I also have separate usage tables storing how many of each part (given a partnumber) for each of the products.

Now this is where the complecation comes in .When I create an order I want to 1. add a record to my order table (containing ordernumber, client number, product purchased) 2. using the product table, find out which usuage tables need to be looked at, go into those usage tables, get the number of each part that is needed for the product and store the value into an allocation table

I have had this on my mind for the past 2 weeks that I have know I will have to do this at some point. I am getting to the point where I cannot do anything else without this being done. I need help something bad.

If you feel you know how to do this and think you need more information, email me at cgow1@cogeco.ca

 

[The_Doc_Man]

get the number of each part that is needed for the product and store the value into an allocation table

First things first... SEARCH this forum for "inventory" and you will find some very deep discussions on how to set up an inventory table.

Second, you need a linking table that lists the components of your product by product number, one record containing (at least) two fields - product number and component number, with product number as a foreign key that is the basis of a one-to-many link (product main table is "one" side; this table is "many" side.) Then finding the parts you need for the end product is a piece of cake. To be rigorously complete, elementary products - things you sell that are a single part, not an assembly, should also have one record in the table, for which the component number could point directly to itself. If you have this, then you never "search" for anything because you can do a JOIN that merges the records for you in exactly the right way.

Third, I can't say for sure but it sounds like you might benefit from reading up on normalization. The problem you described is often - but not always - caused by lack of complete normalization. You said you had separate usage tables (PLURAL WORD) for resolving part number to product number or vice versa. If that ain't a single table and your final product table IS a single table, you have a normalization problem par excellence.