Join Problems

[uaguy3005]

I have 3 tables, I'm working with in this query as shown below:

tblParts tblSortData tblRejects
PartID (PK) SortDataID(PK) RejectID(PK)
PartNum PartsID(FK) SortDataID(FK)
PartDesc QtyInspected QtyRejected
InspectDateTime Defect


ContainerID

The premise being that each record (ie container) in tblSortData can have multiple rejects for different reasons. The query needs to display total qty inspected and total quantity rejected for each part number by day. The current query will duplicate the qtyinpsected if there are multiple records in tblRejects. Any idea on how to show only the unique qtyinspected with the sum of rejects? (I think it could work if I included a PartID(FK) in tblRejects, but I'd prefer not to redo the tables unless I had no other options.)

My current SQL is: SELECT tblSortData.InspectDateTime, tblParts.PartNum, Sum(tblSortData.QtyInspected) AS SumOfQtyInspected, Sum(tblRejects.QtyRejected) AS SumOfQtyRejected FROM (tblParts INNER JOIN tblSortData ON tblParts.PartID = tblSortData.PartsID) LEFT JOIN tblRejects ON tblSortData.SortDataID = tblRejects.SortDataID
GROUP BY tblSortData.InspectDateTime, tblParts.PartNum;

[June7]

What is tblSortData for?

If tblRejects records are not specific to part there is no way to summarize by part.

If you want to provide db for analysis, follow instructions at bottom of my post.

[uaguy3005]

What is tblSortData for?

If tblRejects records are not specific to part there is no way to summarize by part.

If you want to provide db for analysis, follow instructions at bottom of my post.

DB attached. The way I have the tables setup are tblParts stores individual part information, tblSortData represents a container of parts, and stores inspection & container information, and because there could be multiple rejects for different defects in each container I setup tblRejects to store the reject data. I would like to be able to track what rejects came out of each container, which why I related the tables as I did, but any suggestions or help you can provide would be greatly appreciated.

[June7]

Why is tblDefectCodes linked to tblPartsOnSort and tblRejects? Appears to be circular relationship, review http://www.codeproject.com/Articles/...atabase-Design

Is this what you are wanting:

SELECT tblSortData.PartsID, Format([InspectDateTime],"mm/dd/yyyy") AS InspectDate, tblParts.PartNum, Sum(tblSortData.QtyInspected) AS SumOfQtyInspected, Sum(tblRejects.QtyRejected) AS SumOfQtyRejected
FROM (tblParts INNER JOIN tblSortData ON tblParts.PartID = tblSortData.PartsID) INNER JOIN tblRejects ON tblSortData.SortDataID = tblRejects.SortDataID
GROUP BY tblSortData.PartsID, Format([InspectDateTime],"mm/dd/yyyy"), tblParts.PartNum;

[uaguy3005]

Why is tblDefectCodes linked to tblPartsOnSort and tblRejects? Appears to be circular relationship, review http://www.codeproject.com/Articles/...atabase-Design

Is this what you are wanting:

SELECT tblSortData.PartsID, Format([InspectDateTime],"mm/dd/yyyy") AS InspectDate, tblParts.PartNum, Sum(tblSortData.QtyInspected) AS SumOfQtyInspected, Sum(tblRejects.QtyRejected) AS SumOfQtyRejected
FROM (tblParts INNER JOIN tblSortData ON tblParts.PartID = tblSortData.PartsID) INNER JOIN tblRejects ON tblSortData.SortDataID = tblRejects.SortDataID
GROUP BY tblSortData.PartsID, Format([InspectDateTime],"mm/dd/yyyy"), tblParts.PartNum;

tblDefectCodes is really a table I'm using as lookup table. Data won't be entered into it by the user, just admin and infrequently. tblPartsOnSort is also one I'll have data entered into to track parts going on and off sort, but is sort of separate from the activity of sorting, which is what the tblSortData and tblRejects is for.

The query you gave doesn't show me any sort quantities for parts that have 0 rejects or "Null" in tblRejects. This morning I was trying to create an expression to divide the qtyInspected by the number of records in reject ID whenever it's greater than 0, but run into an error about aggregate functions in an expression. Any other suggestions?

SELECT Format([InspectDateTime],"mm/dd/yyyy") AS InspectDate, tblParts.PartNum, Sum(tblSortData.QtyInspected) AS SumOfQtyInspected, Sum(tblRejects.QtyRejected) AS SumOfQtyRejected, Count(tblRejects.RejectID) AS CountOfRejectID,Sum(IIf(Count(tblRejects.RejectID) >0,tblSortData.QtyInspected/count(tblRejects.RejectID) ,tblSortData.QtyInspected)) AS InspectQty
FROM (tblParts INNER JOIN tblSortData ON tblParts.PartID = tblSortData.PartsID) LEFT JOIN tblRejects ON tblSortData.SortDataID = tblRejects.SortDataID
GROUP BY Format([InspectDateTime],"mm/dd/yyyy"), tblParts.PartNum;

[June7]

Maybe just need to change the join type:

SELECT tblSortData.PartsID, Format([InspectDateTime],"mm/dd/yyyy") AS InspectDate, tblParts.PartNum, Sum(tblSortData.QtyInspected) AS SumOfQtyInspected, Sum(tblRejects.QtyRejected) AS SumOfQtyRejected
FROM (tblParts RIGHT JOIN tblSortData ON tblParts.PartID = tblSortData.PartsID) LEFT JOIN tblRejects ON tblSortData.SortDataID = tblRejects.SortDataID
GROUP BY tblSortData.PartsID, Format([InspectDateTime],"mm/dd/yyyy"), tblParts.PartNum;


Build the desired aggregate query. Then build another query that references the aggregate to do further calcs - or build a report that uses the aggregate query as RecordSource.