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
Expand|Select|Wrap|Line Numbers
- SELECT PartNumbers.*, PartProperties.PropertyValue
- FROM PartNumbers, PartProperties
- WHERE (((PartNumbers.PartitionID)=0) AND ((PartNumbers.PartNumberID)=([PartProperties].[PartNumberID])) AND ((PartProperties.PropertyName)="Value" Or (PartProperties.PropertyName)="Type"));
1 0 CAP0402-OMIT ??? OMIT C CAP 0402 OMIT Capacitor
2 0 CAP0402-SAT0 ??? SAT0 C CAP 0402 SAT 0