Linking tables / Queries

[mulefeathers]

I have 1 query that contains 2 fields Shortage and count of shortage. The displays the components needed to build a part that is short and how many times it appears in the shortage table.

Then I have the shortage table that contains the master part number with the qty needed $ and 3 other fields named Shortage 1 shortage 2 and shortage 3.

Can I link 3 fields from one table to 1 field of another table.

example would be linking shortage 1, 2, and 3 in the shortage table to shortage column in the shortage query?

I know this is kinda of vague but if anybody thinks they can help ask me some questions I will answer what ever is needed to push me in the right direction.

[TheShabz]

It would help if you started with explaining what exactly you are considering a "shortage" and why you have 3 columns for shortage.

[mulefeathers]

Shortage is a heading or column name. There are 3 because it takes multiple components to make up a part number. We track the first 3 missing components. What I have done is create a SQL union query. This has moved me in the right direction however now due to the way the data is formatted at the start I am getting duplicate records.

[TheShabz]

Ok, another question. What is the purpose of linking them? You said they combine to form the product number. Is that what your end goal is? If so, I think you are trying to concatenate those fields together.

[mulefeathers]

I have a table made up of about 7000 rows. There are several columns but the important ones are Part Number (which is our part number of a complete valve ready to be sold) QTY (which is the quantity of complete valves on order), $$$ (which is how much the order is worth to the company) and the Shortage 1, Shortage 2, and Shortage 3 columns.

Each complete part to be sold is made up of individual parts either manufactured by us or purchased from other suppliers. No matter where they come from each component needed to build a complete valve has its own part number. Example is in order to sell part number B-0420C1 I have to have a body (with PN ab05-34) a STEM (with PN 7040072) and a COIL (with PN KB01-9034) if any one of these parts is out of stock I can not build B-0420C1.

Part Number AB05-34 can be used with several different completed products. We have planners the fill out a table listing which part number can not be assembled because of missing components. My goal to sort which parts are missing components organize them so we can determine how much money we are not making because we can not fulfill the orders.

What I have done is write 3 separate queries returning Part Number and the short component. Since the table is set up to list shortages in three separate columns I did this in 3 steps. Then I wrote a SQL Union query to compile the list of the 3 queries in 1 column with corresponding part numbers. However since the 3 queries combine to form 1 query I am getting duplicate information for example Part Number 1234 has an order qty of 10 worth $1000. It is missing 3 components body number 1345, coil number 4567and stem number 4532. Because it now has 3 entries it is returned as having a value of $3000 instead of $1000. What I have to figure out is how to get rid of the duplicates. And because one part number may on order from several different customers I can not set a primary key of Part Number.

A long explanation but thanks for any assistance. By the way if you may know of another direction I am not opposed to starting from scratch.

[TheShabz]

Ok from what I'm reading, you have a table that holds both the complete product numbers, the shortages of each individual component (the shortage1, 2, 3), as well as the value lost by not completing the order. I think it is the value lost (a calculated field), that is throwing you off. Generally, you want to avoid having a calculated field in a table. Here's what I would do (it may be an overhaul depending on how your other tables are related).

I would have one table, I'll call it "Product," that holds the ProductID of the finished product, as well as the PartIDs of the 3 components. I would then have another table, "Parts," That holds the PartIDs (Part1, Part2, Part3), descriptions, and inventory count. I would then have a third table, "Orders," which contains whatever you need for your Order. (note: to have an Orders table, you will need a customers table, etc. I am only listing relevant tables here)

Now, to obtain the value lost from orders, I would create a report. In the report, i would take the product IDs from all the unfullfilled orders, and look up the PartIDs associated with that Product. If there is a shortage on any part (I dont care which one or how many), I will mark the value of that Order as a missed opportunity. On the report then, I can calculate the total lost sales for any given time period.

Hope that makes sense and leads on you on the right path.

[mulefeathers]

I see where you are going. Thanks for your help.

[TheShabz]

Anytime. If that's all you need, please mark the thread solved =]