Query to show data if other data doesn't exist

[redcuda69]

Access 2000 on windows XP

Below are examples of 2 tables I am trying to run the following query on. In the query, I only want one line item for each PartNumber. If the PartNumber has a PropertyName of "Value" I want it to list the PropertyValue. If it doesn't have a PropertName of "Value", I still want it to list the PartNumber. This seems like it should be fairly simple, but I am new to Access. Thanks for any help.

Rob


Table: PartNumbers
PartNumberID PartitionID PartNumber PartName PartLabel RefDesPre Description
1 0 CAP0402-OMIT ??? OMIT C CAP 0402 OMIT
2 0 CAP0402-SAT0 ??? SAT0 C CAP 0402 SAT

Table: PartProperties
PropID PartNumberID NameIndex PropertyType PropertyName PropertyValue
1 1 CAP0402-OMIT 1 Type Capacitor
2 2 CAP0402-SAT0 1 Type Capacitor
3 2 CAP0402-SAT0 1 Value 0

Current Query
PartNumberID PartitionID PartNumber PartName PartLabel RefDesPre Description PropertyValue
1 0 CAP0402-OMIT ??? OMIT C CAP 0402 OMIT Capacitor
2 0 CAP0402-SAT0 ??? SAT0 C CAP 0402 SAT Capacitor
2 0 CAP0402-SAT0 ??? SAT0 C CAP 0402 SAT 0

SELECT PartNumbers.*, PartProperties.PropertyValue
FROM PartNumbers, PartProperties
WHERE (((PartNumbers.PartitionID)=0) AND ((PartNumbers.PartNumberID)=([PartProperties].[PartNumberID])) AND ((PartProperties.PropertyName)="Value" Or (PartProperties.PropertyName)="Type"));

Ideal Results
1 0 CAP0402-OMIT ??? OMIT C CAP 0402 OMIT Capacitor
2 0 CAP0402-SAT0 ??? SAT0 C CAP 0402 SAT 0

[nico5038]

First create a query qryPartProperties that's selecting the PartNumberID and the PropertyName and filtering for "Value" of the latter.

Then you'll need to create an outer join query between the PartNumbers table and qryPartProperties.
Just placeboth of them into the graphical query editor and drag and drop the PartNumberID from one table to the other.
Now double-click the connection line and chose option 2 or 3 making the PartNumbers table "leading".
Finally place the PartNumberID and other fields from the PartNumbers table and add the needed fields from qryPartProperties..
Because of the OUTER JOIN only the "Value" rows will hold data, the other fields from the qryPartProperties will be empty.

Nic;o)

[redcuda69]

Exactly what I needed!
Thanks