billyw1953
Registered User.
- Local time
- Yesterday, 21:43
- Joined
- Jan 15, 2004
- Messages
- 13
My crude little query is actually working great with one problem. I have 18 employees working a program and the query will only show me 17. The one it's not showing is someone who DID NOT work at all during the [Beginning Date] and [Ending Date] I specified (1-1-04 to 1-23-04). I deduced quite incorrectly I suppose that his/her [Total Time] would be a null value and I could make some adjustments to the query in order to show everyone. It does not pass the null test. I have tried several criteria and IIf statements but I can't get it going. I'm thinking it has something to do with the date range I am specifiying but I cannot figure this out. Is it something simple I am overlooking? Sorry to seem like a pest but I have actually figured the solution to about 90% of the problems I have by just reading a lot of the posts in here. The other 10% are giving me a migraine and I'm thinking of launching a terrorist attack against my computer. Here is the sql if anyone can help.
SELECT ([offlastname]) & ", " & ([offfirstname]) AS Name, Sum(Nz(([DateTimeNoNEnds]-[DateTimeNoNBegins])*24,0)) AS [Total Time], Sum(([citspeed])+([citslight])+([citssign])+([citinsurance])+([citwarning])+([citother])) AS [Total Citations], Sum(([citspeed])+([citslight])+([citssign])+([citinsurance])+([citwarning])+([citother]))/Sum(([DateTimeNoNEnds]-[DateTimeNoNBegins])*24) AS [Avg Citations Per Hr], Sum(DateTimeNONTable.CitWarning) AS Warnings
FROM (OfficerTable INNER JOIN TSPAssignmentTable ON OfficerTable.OffID = TSPAssignmentTable.OffID) INNER JOIN DateTimeNONTable ON TSPAssignmentTable.TSPID = DateTimeNONTable.TSPID
WHERE (((DateTimeNONTable.DateTimeNONBegins) Between [Type Beginning Date] And [Type Ending Date]) AND ((DateTimeNONTable.DateTimeNONEnds) Between [Type Beginning Date] And [Type Ending Date]))
GROUP BY ([offlastname]) & ", " & ([offfirstname]), TSPAssignmentTable.ProgramAssign
HAVING (((TSPAssignmentTable.ProgramAssign)="tsp"))
ORDER BY Sum(([citspeed])+([citslight])+([citssign])+([citinsurance])+([citwarning])+([citother]))/Sum(([DateTimeNoNEnds]-[DateTimeNoNBegins])*24) DESC;
Thanks again to all of you who take the time to help out.
Billy
SELECT ([offlastname]) & ", " & ([offfirstname]) AS Name, Sum(Nz(([DateTimeNoNEnds]-[DateTimeNoNBegins])*24,0)) AS [Total Time], Sum(([citspeed])+([citslight])+([citssign])+([citinsurance])+([citwarning])+([citother])) AS [Total Citations], Sum(([citspeed])+([citslight])+([citssign])+([citinsurance])+([citwarning])+([citother]))/Sum(([DateTimeNoNEnds]-[DateTimeNoNBegins])*24) AS [Avg Citations Per Hr], Sum(DateTimeNONTable.CitWarning) AS Warnings
FROM (OfficerTable INNER JOIN TSPAssignmentTable ON OfficerTable.OffID = TSPAssignmentTable.OffID) INNER JOIN DateTimeNONTable ON TSPAssignmentTable.TSPID = DateTimeNONTable.TSPID
WHERE (((DateTimeNONTable.DateTimeNONBegins) Between [Type Beginning Date] And [Type Ending Date]) AND ((DateTimeNONTable.DateTimeNONEnds) Between [Type Beginning Date] And [Type Ending Date]))
GROUP BY ([offlastname]) & ", " & ([offfirstname]), TSPAssignmentTable.ProgramAssign
HAVING (((TSPAssignmentTable.ProgramAssign)="tsp"))
ORDER BY Sum(([citspeed])+([citslight])+([citssign])+([citinsurance])+([citwarning])+([citother]))/Sum(([DateTimeNoNEnds]-[DateTimeNoNBegins])*24) DESC;
Thanks again to all of you who take the time to help out.
Billy