Combine records from two sets of related tables into one Query 1

[FoxProProgrammer]

Hi!

Here's a description of my table structure.

tbl_jobs
- jobID (primary key)
- jobNum
- dateCompleted

tbl_LRU_parts_replaced
- jobID (foreign key to tbl_jobs)
- partID (foreign key to tbl_SRU)
- partSN (serial number of SRU replaced)

tbl_SRU
- SRUID
- description
- partNum

tbl_chassis_parts_replaced
- jobID (foreign key to tbl_jobs)
- partID (foreign key to tbl_LRU_chassis_parts)

tbl_LRU_chassis_parts
- partID
- description
- partNum

I need a Query that includes the description and part number of all parts replaced on a job. The jobID is the value of a combo box on a form (cbo_job). The parts replaced on a job could be in tbl_SRU and/or tbl_LRU_chassis_parts. The Query that I wrote doesn't quite work. Maybe I need to use a Union instead of simple Select Query?

I sincerely appreciate any help!

Thanks very much,


dz

 

[PHV]

The Query that I wrote doesn't quite work
Which SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[FoxProProgrammer]

Howdy, PHV.

Here is the SQL

SELECT   tbl_LRU_chassis_parts.Description, tbl_LRU_chassis_parts.PN, tbl_SRU.Part_Name, tbl_SRU.Part_PN
FROM tbl_SRU INNER JOIN (tbl_LRU_chassis_parts INNER JOIN ((tbl_Jobs INNER JOIN tbl_LRU_parts_replaced ON tbl_Jobs.JobID = tbl_LRU_parts_replaced.JobID) INNER JOIN tbl_chassis_parts_replaced ON tbl_Jobs.JobID = tbl_chassis_parts_replaced.JobID) ON tbl_LRU_chassis_parts.PartID = tbl_chassis_parts_replaced.PartID) ON tbl_SRU.SRUID = tbl_LRU_parts_replaced.PartID
WHERE (((tbl_Jobs.JobID)=[cbo_job].[Value]));

Here is a sample of the result:

Description PN Description PN
Fault Indicator 8209-88733 Transducer Board 101010
Fault Indicator 8209-88733 Air Data Board 908939
.....

This is why I think that I need to use a Union Query. The result needs to look like:

Description PN
Fault Indicator 8209-88733
Transducer Board 101010
Air Data Board 908939

Thank you!


dz

 

[FoxProProgrammer]

My Query almost works.

SELECT tbl_SRU.Part_Name As Description, tbl_SRU.Part_PN As [Part Number]
FROM tbl_SRU INNER JOIN (tbl_Jobs INNER JOIN tbl_LRU_parts_replaced ON tbl_Jobs.JobID = tbl_LRU_parts_replaced.JobID) ON tbl_SRU.SRUID = tbl_LRU_parts_replaced.PartID
WHERE (((tbl_Jobs.JobID)=[cbo_job].[Value]))
UNION 
SELECT tbl_LRU_chassis_parts.Description As Description, tbl_LRU_chassis_parts.PN As [Part Number]
FROM tbl_LRU_chassis_parts INNER JOIN (tbl_Jobs INNER JOIN tbl_chassis_parts_replaced ON tbl_Jobs.JobID = tbl_chassis_parts_replaced.JobID) ON tbl_LRU_chassis_parts.PartID = tbl_chassis_parts_replaced.PartID
WHERE (((tbl_Jobs.JobID)=[cbo_job].[Value]));[code]

Here is the output:

Description             Part Number
Fault Indicator         8209-88733
Transducer Board        101010
Air Data Board          908939


The output should actually look like this because two Transducer boards were replaced:

Description             Part Number
Fault Indicator         8209-88733
Transducer Board        101010
Transducer Board        101010
Air Data Board          908939

Each Query returns the correct results when run alone, but the combined Query acts like a DISTINCT.  Any idea why?

I eventually need to get the serial number in the Query output.  I tried to add the fld (tbl_LRU_parts_replaced.PartSN, but it gives me an error because the number of columns in the two parts of the Union don't match.

Thanks,



dz

 

[PHV]

What about replacing UNION with UNION ALL ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[FoxProProgrammer]

Thanks! That did the trick. Why do you get a star if I did most of the work?

I'll worry about the serial number field later...I might be able to do it a different way without including it in this Query.

dz