LEFT JOIN with WHERE selection criteria

[starbird]

Hopefully, someone can help me.

I have a qry that I am looking to get all resources, the total time each resource worked, and also show the resources that didn't book any time. This query works perfectly when I have no selection critera, and shows all 540 resources.

When I enter selection criteria to limit by date range, I get 467 records. I have done some research, and what I am getting is all those resources that have booked time in the given range, with thier total time for that date range, and all resources that have NEVER booked time. What I am missing is the resources that, at some point, booked time, but not in this time frame.

My SQL statement is as follows:

SELECT tblMain_ResourcePool.Cost_Center, tblMain_ResourcePool.Corp_ID, Sum(tblMain_AutoTeamTime.Hours) AS SumOfHours
FROM tblMain_ResourcePool LEFT JOIN tblMain_AutoTeamTime ON tblMain_ResourcePool.Corp_ID = tblMain_AutoTeamTime.Badge_ID
WHERE (((tblMain_AutoTeamTime.Date) Between [Forms]![frmCostCenterSelection]![txtFrom] And [Forms]![frmCostCenterSelection]![txtTo] Or (tblMain_AutoTeamTime.Date) Is Null))
GROUP BY tblMain_ResourcePool.Cost_Center, tblMain_ResourcePool.Corp_ID
HAVING (((tblMain_ResourcePool.Cost_Center)<>&quot;DD&quot; And (tblMain_ResourcePool.Cost_Center)<>&quot;1324&quot; And (tblMain_ResourcePool.Cost_Center)<>&quot;6724&quot;))
ORDER BY tblMain_ResourcePool.Cost_Center, tblMain_ResourcePool.Corp_ID;

Any suggestions? I pull the dates from a form. Thank you!

 

[mp9]

I have tested it, but I'd say your

Or (tblMain_AutoTeamTime.Date) Is Null

bit could be the reason why you're getting resources that have never booked time.

http://www22.brinkster.com/accessory/

 

[starbird]

Thanks for your reply.

I do want to get resources that have never booked time.

What I am not getting is the resources that, at some point, have booked time, just not in the time frame given. (for example - 10/07/2002 throught 10/11/2002)

Any suggestions?

 

[mp9]

Surely the other half of your WHERE clause, which specifies that the date field must be between two dates on your form is why you're not getting records where the date is outside your date range?

If you remove the entire WHERE clause I think you'd return all records... and that's what you want, right?

Apologies if I'm being dense and comlpetely missing the point!

http://www22.brinkster.com/accessory/

 

[starbird]

No appology necessary.

I do want all records (all resources, that is) but I only want the time calculated for the time frame given (the WHERE clause). When I remove the WHERE, I get exactly the right number or resources, and everything is fine. But I want to only look at a specific time frame (from the form). And when I put the WHERE clause in, I get all the resources that have never entered time at any timeframe, and the total hours for those resources who did enter time for this time frame, but I do not get the resources who, for example, entered time for September, but not October (if I was running the report for October)

Thanks for your help, and hopefully I cleared it up a little.

 

[zerosandones]

Hi Starbird,

The solution is simple. The error in your SQL is that you only allow for two conditions in your WHERE clause ... records which are in the date range or records which have no date specified ... in doing so, you have filtered "out" those records with a date outside your specified range.

To solve your problem, you need to eliminate the WHERE clause and incorporate logic and operators as I have listed in a modified version of your original SQL:

SELECT tblMain_ResourcePool.Cost_Center, tblMain_ResourcePool.Corp_ID, Sum
(Switch(tblMain_AutoTeamTime.Date Between [frmCostCenterSelection]![txtFrom] And [frmCostCenterSelection]![txtTo],tblMain_AutoTeamTime.Hours,True,0)) AS SumOfHours
FROM tblMain_ResourcePool LEFT JOIN tblMain_AutoTeamTime ON tblMain_ResourcePool.Corp_ID = tblMain_AutoTeamTime.Badge_ID
GROUP BY tblMain_ResourcePool.Cost_Center, tblMain_ResourcePool.Corp_ID
HAVING (((tblMain_ResourcePool.Cost_Center)<>&quot;DD&quot; And (tblMain_ResourcePool.Cost_Center)<>&quot;1324&quot; And (tblMain_ResourcePool.Cost_Center)<>&quot;6724&quot)
ORDER BY tblMain_ResourcePool.Cost_Center, tblMain_ResourcePool.Corp_ID;

Hope this helps,

00001111s