Not an insurmountable query problem (1 Viewer)

[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

 

[Pat Hartman]

When you join two tables using an inner join, each table needs to contain matching rows in order for a row to be selected. When one table has all the rows you need and the other table has some but not all as in your case, you need to change the join type to left or right depending on which table is the one with all the rows you want to see.

 

[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

 

[Pat Hartman]

Changing the join type will solve the problem. Why not post the query with the Left join. You should also get rid of the Having clause and move that constraint to the where clause. The Where clause is applied after the join but before the summarization. The having clause is applied after the summarization. Since ProgramAssign is not part of the select clause, it is discarded before the data is summarized. Therefore the having criteria is not being properly applied.

 

[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.

 

[Pat Hartman]

I don't see your problem. I think you may be misreading the results. I got 17 rows also but Wilke IS in the query. I also ran the following query to confirm that there were not more than 17 officers that should be selected.

SELECT DateTimeNONTable.TSPID
FROM DateTimeNONTable
WHERE (((DateTimeNONTable.DateTimeNONBegins)>=#12/1/2003#) AND ((DateTimeNONTable.DateTimeNONEnds)<=#12/31/2003#))
GROUP BY DateTimeNONTable.TSPID;

I don't know where you're getting the number 18 from.

 

[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.

 

[Pat Hartman]

You need to break up the query. Remove the OfficerTable from the existing query. Then create a new query that joins the OfficerTable to the modified query using a left join. The problem is that the selection criteria are all applied to the tables to the "right" and that is preventing the complete list of officers from being selected. Once you separate the query as I have described, you force Jet to process the query your way and you will then see all the officiers. In the existing query, the selection criteria is being applied after the join which is why rows are being dropped. By splitting the query, you force Jet to apply the selection criteria before joining to the OfficerTable. Hope that makes sense. When the criteria is applied is something you need to worry about only when you are using outer joins and the criteria does not apply to the "leftmost" or "rightmost" table.

 

[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