MS Access Query Issue

[tjfitz68]

Hello,

List below is a query that I need to somehow be able to modify so my results do not double the tblWorkOrder.iWorkOrderID rows. The reason it's happening is because the INNER join for the tblTechnician causes it to double the iWorkOrderID's when there are two or more technicians that are listed on the Workorder. Hopefully I am explaining this correctly. Oh, I didn't create the query below but I have to fix it. If I can't accomplish what I need to by modifying the query, I'm going to have to re-write the complete report this is used for.

*************

SELECT tblDistrict.iCode, tblSchool.iUserCode, tblSchool.strSchoolName, Sum(tblWorkOrder.dStock) AS SumOfdStock, Sum(tblWorkOrder.dDirect) AS SumOfdDirect, Sum(tblWorkOrder.dLabor) AS SumOfdLabor, Sum(tblWorkOrder.dTotal) AS SumOfdTotal, Sum(tbltechnician.hoursWorked) AS SumOfhoursWorked, tblDistrict.iDistrictID, tblDistrict.strDistrictName
FROM (tblDistrict INNER JOIN (tblSchool INNER JOIN (tblWorkOrder INNER JOIN tblWorkOrderRequest ON tblWorkOrder.iWorkOrderRequestID = tblWorkOrderRequest.iWorkOrderRequestID) ON tblSchool.iSchoolID = tblWorkOrderRequest.iSchoolID) ON tblDistrict.iDistrictID = tblSchool.iDistrictID) INNER JOIN tbltechnician ON tblWorkOrder.iWorkOrderID = tbltechnician.iworkorderID
WHERE (((tblDistrict.IDistrictID)<>0) AND ((tblSchool.iSchoolID)<>0) AND ((tblWorkOrder.iOrderStatusID)=4 Or (tblWorkOrder.iOrderStatusID)=3) AND ((tblWorkOrder.iRequestID)<>2) AND ((tblWorkOrder.dDateCompleted) Between #3/1/2011# And #3/31/2011#))
GROUP BY tblDistrict.iCode, tblSchool.iUserCode, tblSchool.strSchoolName, tblDistrict.iDistrictID, tblDistrict.strDistrictName;

************

Thanks,

Tim

[stbuchok]

Can you explain the table structure? Also it would be nice if you'd structure the SQL so that it is easier to read.

[BitBlt]

Before I try to answer this, let me just say that in my opinion, MSAccess SQL is an abomination (and I've said it before in other posts).

Okay, now that I've got that off my chest...

It appears that this query is grouping one level too high. If you were to add one extra level of grouping, say at the technician ID level from tbltechnician, then you could get a clean set of rows, with the right hours summed at the right level. It might look something like this (extra formatting is mine, and I'm making up the extra column name):

SELECT 
tblDistrict.iCode, 
tblSchool.iUserCode, 
tblSchool.strSchoolName, 
Sum(tblWorkOrder.dStock) AS SumOfdStock, 
Sum(tblWorkOrder.dDirect) AS SumOfdDirect, 
Sum(tblWorkOrder.dLabor) AS SumOfdLabor, 
Sum(tblWorkOrder.dTotal) AS SumOfdTotal, 
Sum(tbltechnician.hoursWorked) AS SumOfhoursWorked, 
tblDistrict.iDistrictID, 
tblDistrict.strDistrictName,
tblTechnician.itechnicianid
FROM 
(
tblDistrict 
INNER JOIN 
	(
	tblSchool 
	INNER JOIN 
		(
		tblWorkOrder 
		INNER JOIN tblWorkOrderRequest 
		ON tblWorkOrder.iWorkOrderRequestID = tblWorkOrderRequest.iWorkOrderRequestID
		) 
		ON tblSchool.iSchoolID = tblWorkOrderRequest.iSchoolID
	)
	ON tblDistrict.iDistrictID = tblSchool.iDistrictID
	) 
INNER JOIN tbltechnician ON tblWorkOrder.iWorkOrderID = tbltechnician.iworkorderID
WHERE 
(
	(
	(tblDistrict.IDistrictID)<>0
	) 
AND 
	(
	(tblSchool.iSchoolID)<>0
	) 
AND 
	(
	(tblWorkOrder.iOrderStatusID)=4 
	Or (tblWorkOrder.iOrderStatusID)=3
	) 
AND 
	(
	(tblWorkOrder.iRequestID)<>2
	) 
AND 
	(
	(tblWorkOrder.dDateCompleted) Between #3/1/2011# And #3/31/2011#
	)
)
GROUP BY tblDistrict.iCode, tblSchool.iUserCode, tblSchool.strSchoolName, tblDistrict.iDistrictID, tblDistrict.strDistrictName, tblTechnician.itechnicianid

Then, create a higher level querydef that uses this one as its source, grouping at the original level (that is, without the technician) and summing all the summed columns and adjusting the names so they don't disrupt your report.

Hope that helps!