Help on level based query 1

[cdavisdccb]

I have a multilevel bill of material that I want to sort via an access query.

I can't seem to get the query to pull all records. Basically, the concept is similar as below:

Lev1: Assembly A
Lev2: Part X
Lev3: Part XX
Lev3: Part YY
Lev2: Part Z

(Where Part XX + Part YY make up Part X, and Part X + Part Z make up Assembly A.)

Lev1: Assembly B
Lev2: Part G
Lev2: Part H

(Where Part G + Part H make up Assembly B)

If I run a normal select query to show these levels, Assembly B nor any of its components will be returned, because they do not have any level 3 components.

Is there a way to format the query to display all assemblies AND their components irregardless of null values at certain levels.

Any help will be appriciated.

Thanks,
Chris


"It doesn't matter how idiot-proof you make it, someone makes a better idiot!"

 

[SonOfEmidec1100]

You will need to use OUTER JOINS, depending on your query either LEFT or RIGHT

For example

SELECT t1.*,t2.* FROM t1 LEFT JOIN t2 on t1.key1=t2.key1

Will return all rows in t1, if there is no data in t2 then these fields will be null.

 

[cdavisdccb]

SonOfEmidec---

Thanks! I knew there was a simple solution. After several hours staring at a screen, my brain just wouldn't work any more! --- Thanks for reviving it!

---FYI to others, to avoid the ambiguous outer join error, I had to create seperate join queries, then a final one to show all fields from the previous ones.

C-

"It doesn't matter how idiot-proof you make it, someone makes a better idiot!"