combining records to show as one in a report.

[mejia.j88]

i was wondering what the easiest/most efficient/etc way of combining records in a report.

i have attached a pdf of the report i run. If we look under Second Shift, part number X25UFG, there are 2 entries that are the same: 160 start quantity and 157 end quantity, along with the same Glassing Lot and same Glass Batch. those are two distinct records but i would rather display them as a whole.

i would want start qty to be 320 and end qty to be 314. i DONT want to sum up all of the records with the same glass lot and glass batch, but rather those that are identical all across the report.



below is the SQL for the query that feeds my report.

SELECT CDate(Format([Date_Time],"mm/dd/yyyy")) AS ShiftDate, Glassing_Yield.PN, Switch(TimeValue([glassing_yield].[Date_Time]) Between TimeSerial(0,0,0) And TimeSerial(5,0,0),"3rd Shift",TimeValue([glassing_yield].[Date_Time]) Between TimeSerial(5,30,0) And TimeSerial(14,0,0),"1st Shift ",TimeValue([glassing_yield].[Date_Time]) Between TimeSerial(14,0,0) And TimeSerial(22,0,0),"2nd Shift",TimeValue([glassing_yield].[Date_Time]) Between TimeSerial(22,0,0) And TimeSerial(0,0,0),"3rd Shift") AS WorkShift, [Start_Qty]-[End_Qty] AS [Reject Qty], [End_Qty]/[Start_Qty] AS [Reject Pct], Glassing_Yield.Start_Qty, Glassing_Yield.End_Qty, Glassing_Yield.Date_Time, QRY_opt_report_query.Evaporation_Lot, QRY_opt_report_query.Diffusion_Lot, Glassing_Yield.Operator, Glassing_Yield.Glassing_Lot, Glassing_Yield.Batch_No, Glassing_Yield.WO, Glassing_Yield.Glassing_MachineFROM QRY_opt_report_query INNER JOIN Glassing_Yield ON QRY_opt_report_query.work_order = Glassing_Yield.WO
WHERE (((Glassing_Yield.Date_Time) Is Not Null))
GROUP BY CDate(Format([Date_Time],"mm/dd/yyyy")), Glassing_Yield.PN, Switch(TimeValue([glassing_yield].[Date_Time]) Between TimeSerial(0,0,0) And TimeSerial(5,0,0),"3rd Shift",TimeValue([glassing_yield].[Date_Time]) Between TimeSerial(5,30,0) And TimeSerial(14,0,0),"1st Shift ",TimeValue([glassing_yield].[Date_Time]) Between TimeSerial(14,0,0) And TimeSerial(22,0,0),"2nd Shift",TimeValue([glassing_yield].[Date_Time]) Between TimeSerial(22,0,0) And TimeSerial(0,0,0),"3rd Shift"), [Start_Qty]-[End_Qty], [End_Qty]/[Start_Qty], Glassing_Yield.Start_Qty, Glassing_Yield.End_Qty, Glassing_Yield.Date_Time, QRY_opt_report_query.Evaporation_Lot, QRY_opt_report_query.Diffusion_Lot, Glassing_Yield.Operator, Glassing_Yield.Glassing_Lot, Glassing_Yield.Batch_No, Glassing_Yield.WO, Glassing_Yield.Glassing_Machine
HAVING (((CDate(Format([Date_Time],"mm/dd/yyyy"))) Between [Forms]![frmShiftYieldByDateRange]![txtStartDate] And [Forms]![frmShiftYieldByDateRange]![txtEndDate]) AND ((Glassing_Yield.Start_Qty)<>0))
ORDER BY CDate(Format([Date_Time],"mm/dd/yyyy")) DESC;

[June7]

The query is a GROUP BY, so what is keeping these two records separate? If they really do have identical data, the GROUP BY should aggregate them into one.