Not an insurmountable query problem

[billyw1953]

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

 

[billyw1953]

Thanks Pat your suggestions are always very much appreciated,

I had already tried changing the join types before I ever posted but still cannot get the query to show all 18 employees. If only 17 worked in January, it will not include the one who didn't work.

Billy

 

[billyw1953]

Changed the ProgramAssign clause to "where" and the join types to a left join (if I understand you correctly). In my mind, if I have applied the Nz function correctly under "TotalTime" then someone who has not worked during the period I specify should show a "0" and my criteria is asking for >=0 which should show all 18 people correct? I still get only 17 people, the missing name should appear as Wike, Barry. I guess I'm not holding my mouth correctly.

Access 2002 WinXP

Billy

 

[namliam]

This is due to the fact your counting/summing on the same table as where you are putting the between clause(s).

Try Below as your solution:

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 LEFT JOIN TSPAssignmentTable ON OfficerTable.OffID = TSPAssignmentTable.OffID) LEFT JOIN DateTimeNONTable ON TSPAssignmentTable.TSPID = DateTimeNONTable.TSPID
WHERE (((TSPAssignmentTable.ProgramAssign)="tsp") AND ((DateTimeNONTable.DateTimeNONBegins) Between #01-jan-2004# And #31-jan-2004#) AND ((DateTimeNONTable.DateTimeNONEnds) Between #01-jan-2004# And #31-jan-2004#))
GROUP BY ([offlastname]) & ", " & ([offfirstname])
HAVING (((Sum(Nz(([DateTimeNoNEnds]-[DateTimeNoNBegins])*24,0)))>=0))
ORDER BY Sum(([citspeed])+([citslight])+([citssign])+([citinsurance])+([citwarning])+([citother]))/Sum(([DateTimeNoNEnds]-[DateTimeNoNBegins])*24) DESC;
union
SELECT ([offlastname]) & ", " & ([offfirstname]) AS Name, 0 AS [Total Time], 0 AS [Total Citations], 0 AS [Avg Citations Per Hr], 0 AS Warnings
FROM ([select OfficerTable.OffID
FROM (OfficerTable LEFT JOIN TSPAssignmentTable ON OfficerTable.OffID = TSPAssignmentTable.OffID) LEFT JOIN DateTimeNONTable ON TSPAssignmentTable.TSPID = DateTimeNONTable.TSPID
WHERE (((TSPAssignmentTable.ProgramAssign)="tsp") AND ((DateTimeNONTable.DateTimeNONBegins) Between #1/1/2004# And #1/31/2004#) AND ((DateTimeNONTable.DateTimeNONEnds) Between #1/1/2004# And #1/31/2004#))]. AS SelectedPPL RIGHT JOIN OfficerTable ON SelectedPPL.OffID = OfficerTable.OffID) INNER JOIN TSPAssignmentTable ON OfficerTable.OffID = TSPAssignmentTable.OffID
WHERE (((SelectedPPL.OffID) Is Null) AND ((TSPAssignmentTable.ProgramAssign)="tsp") AND ((TSPAssignmentTable.DateBegin)<=#1/31/2004#) AND ((TSPAssignmentTable.DateEnd)<=#1/1/2004#));

Its not exactly what your looking for, but it will give you the general idea.... Good luck...

Regards

 

[billyw1953]

Thanks for the help, I'll play with it a while and see if I can get exactly what I need.

 

[billyw1953]

Sorry if I wasn't clear enough in my description Pat but I am not misreading the results. If you specicify a date range from 1/1/04 to 1/27/04 you will get 17 rows and Wike is #18 and not in there. He's not in there because he did not work at all in that date range. The problem is I need to see him even if he did not work. I am trying to see all 18 employees.

Now the date range you show is >=12/1/03 and <= 12/31/03. He did work in December 2003 and he shows up there, the reason for the 17 rows in December is because someone else did not work in December and I am not seeing them either. What a deal huh?

There are 18 total employees that should always show on this query.

 

[billyw1953]

Got it, I will do just that and thanks for the tip. I tried messing with the sql the mailman sent in and got it to work by playing with the date ranges a little. Don't know how or why it's working though, isn't that scary? Anyway, I don't trust what I did in sql, it would work for an end of January report as I checked the numbers, but I don't know what would happen as February and March approach so I will break up the query and create a new one.

Thanks to all,

Billy