Building complex queries

[EDGE99]

I am trying to build a complex query that will help me extract data that is not really easy to analyse


Widget A = Product ID 100

Widget B = is made up of multiple id's
= Product ID 200
= Product ID 150 <- this product can be used by with both Widget A and Widget B, C, D ...

I am not sure why they set the data up this way but that is how it is in their database. So it is hard to extract the data. I need to extract all the data for each Widget type (A,B,C,D) and how many (Product ID 150's) are sold with each type of Widget.

Data Fields:

Product_ID
Product_Description
Invoice_ID
Sale_Price

Problem : A invoice can have the following

Product ID = 100 (Widget A)
Product ID = 150 (Universal Part)

or

Product ID = 100 (Widget A)
Product ID = 150 (Universal Part)
Product ID = 200 (Widget B)

or

Product ID = 100 (Widget A)
Product ID = 150 (Universal Part)
Product ID = 200 (Widget B)
Product ID = 150 (Universal Part)

or many other combinations as well



Currently I create three queries. One that groups by (invoice_id) where (Product ID = <some value>)

So this gives me all invoice_id's that have a certain product id.

The second that list all (Product ID = 150)

A Third that link the two together and add a relationship between the (Invoice_ID)

I do this for all of the other (Product ID's, 100, 200, 300...) and I get my totals. However there is a flaw to this as some invoices can contain multiple Product ID's so I am potentialy duplicating my (Product ID = 150) counts.

So to solve the problem I created another query where I am still grouping on (invoice_id) and where (Product ID = "100" "200" "300")

Now a new problem. Now it is possible that I am leaving records out because I am grouping on invoice id it could potentialy not grab all of the data.

Is there a way I can determine what Product id 150's go with what Product id 100, 200, 300?

Please let me know if this makes sense?? I feel I am rambling and not really getting my point accross.

Your help would be most greatly appreciated.

 

[Crowley16]

is there a set number of "levels" of widgets?

i.e. a b can contain a 100 and 150 but not another b or another part with multiple ids.

if so, then you can just use joins or subqueries to get your data without having to build multiple queries...

--------------------
Procrastinate Now!

 

[EDGE99]

Actually there are more parts under the 200 part id. (150, 160, 170) and there is a potential for another part to have multiple ids.

I am very new to access and not sure what a join actually does. I will review my access book to get a understanding of the join.

 

[PHV]

Have a look here:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886