Access SQL: concatenate rows containing a specific value

Question

I am trying to create a table which will list part assemblies and their constituent parts. I have made some code which will do this but in this example I get 7 rows when I want only 2 (the PartDefID and PartClass columns will be hidden the the DGV that displays the table). Each row should contain parts that have the same AssemblyID although several parts may reside on the same row. The code I have so far is:

SELECT Parts.PartDefID, PartDefinitions.PartClass, Parts.AssemblyID, PartDefinitions.PartNumber, PartDefinitions.PartDescription, PartDefinitions.KitNumber
FROM Parts INNER JOIN PartDefinitions ON Parts.PartDefID = PartDefinitions.PartDefID
WHERE PartDefinitions.PartClass = 1 OR PartDefinitions.PartClass = 3 OR PartDefinitions.PartClass = 4 OR PartDefinitions.PartClass = 5 OR PartDefinitions.PartClass = 6 AND Reference = 0;

this gives me a table that looks like this:

PartDefID      PartClass  AssemblyID                                    PartNumber      PartDescription                                   KitNumber
68             1          {86d99c66-3cc3-40cf-9489-7be7838f90c8}        330-00366-78    GI275 P1                                          011-04809-00
23             3          {86d99c66-3cc3-40cf-9489-7be7838f90c8}        011-00950-04     Sub-Assy, Backshell w/Hdw, 50/78 pin   
2              1          {17e17f78-731a-4828-972b-be91c06eb149}        MS27467T23F55P   Connector, circular 55 pin 
31             3          {17e17f78-731a-4828-972b-be91c06eb149}        M85049-49-2-22N  Sub-Assy, Backshell w/Hdw, 55 pin circular 
41             6          {86d99c66-3cc3-40cf-9489-7be7838f90c8}        011-04038-00     Sub-Assy, Config Mdl, w/EEPROM, Jackscrew, 2Mbit   
32             4          {86d99c66-3cc3-40cf-9489-7be7838f90c8}        011-01169-00     Sub-Assy, Shield block w/Hdw, shell size 1-3   
34             5          {86d99c66-3cc3-40cf-9489-7be7838f90c8}        117-00004-00     Locking tab 9, 15, 25, 37

What I want is a table that looks like this:

PartDefID       PartClass    AssemblyID                                 PartNumber      PartDescription              Backshell        Shield block   Slide lock      Config module   KitNumber
68              1            {86d99c66-3cc3-40cf-9489-7be7838f90c8}     330-00366-78    GI275 P1                     011-00950-04     011-01169-00   117-00004-00    011-04038-00    011-04809-00
2               1            {17e17f78-731a-4828-972b-be91c06eb149}     MS27467T23F55P  Connector, circular 55 pin   M85049-49-2-22N

In the table I want, the "backshell" column is the partnumber for partclass 3, the "shield block" column is the partnumber of partclass 4, the "slide lock" column is the partnumber of partclass 5 and the "config module" column is the partnumber partclass 6. The only part descriptions that would be listed then would be those for partclass 1 which is the parent part. I think it's doable, but out of my league. Thanks for any tips.

Answer 1

Parentheses are critical when mixing OR and AND operators - think how parentheses are considered in algebraic rules of order of operations. Your query WHERE CLAUSE logic is "return all records where (PartClass equals 1 or 3 or 4 or 5) or (PartClass equals 6 and Reference equals 0)" - even though your query has no parentheses, that's the logic that will be followed - perform operations left to right.

Possibly you want:

WHERE (PartDefinitions.PartClass = 1 
OR PartDefinitions.PartClass = 3 
OR PartDefinitions.PartClass = 4 
OR PartDefinitions.PartClass = 5 
OR PartDefinitions.PartClass = 6) 
AND Reference = 0;

or WHERE PartClass IN (1, 3, 4, 5, 6) AND Reference = 0

Now only records that have any of those PartClass values AND have Reference of 0 will be retrieved. You don't show Reference field so assuming only records 68 and 2 have 0, then only those 2 will retrieve.

If there are only 6 PartClass values, could simplify:

WHERE PartClass <> 2 AND Reference = 0

Now, in light of clarification in comments, producing the desired output would involve either:

1. a CROSSTAB query - use Access query designer, there is even a wizard for CROSSTAB; more info about CROSSTAB http://allenbrowne.com/ser-67.html

2. emulate a CROSSTAB with conditional expressions to calculate fields and use GROUP BY to compress - example in SQL Pivot table, with multiple pivots on criteria - one expression for your data: Backshell: First(IIf([PartClass]=3, [PartNumber], Null))