Access 2003 query. Data not displayed if results are null

[Webkins]

I am using the following Access query to return overtime data. I am getting only week numbers which contain overtime hours. If the week number overtime hours are null then the week number does not appear in the query results. I would like to see all the week numbers whether the overtime hours are null or contains data:

SELECT DISTINCTROW tbl_weekly_2011.ClockNumber, tbl_weekly_2011.WeekNumber, tbl_rate.Person, Sum(tbl_weekly_2011.Overtime) AS OT
FROM tbl_rate INNER JOIN tbl_weekly_2011 ON tbl_rate.ClockNumber = tbl_weekly_2011.ClockNumber
GROUP BY tbl_weekly_2011.ClockNumber, tbl_weekly_2011.WeekNumber, tbl_rate.Person
HAVING (((tbl_rate.Person)=[Forms]![frm_start]![person_name]));

Thank you for any and all assistance.

 

[dhookom]

You have "INNER JOIN" when you actually should be using a LEFT or RIGHT JOIN. You may also need to set the criteria to allow nulls. I would try something like:

SELECT DISTINCTROW tbl_weekly_2011.ClockNumber, tbl_weekly_2011.WeekNumber, 
tbl_rate.Person, Sum(tbl_weekly_2011.Overtime) AS OT
FROM tbl_rate RIGHT JOIN tbl_weekly_2011 ON tbl_rate.ClockNumber = tbl_weekly_2011.ClockNumber
WHERE Person=[Forms]![frm_start]![person_name] OR Person Is Null
GROUP BY tbl_weekly_2011.ClockNumber, tbl_weekly_2011.WeekNumber, tbl_rate.Person;

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

I'd try this:

SELECT tbl_weekly_2011.ClockNumber, tbl_weekly_2011.WeekNumber, tbl_rate.Person, Sum(tbl_weekly_2011.Overtime) AS OT
FROM tbl_rate LEFT JOIN tbl_weekly_2011 ON tbl_rate.ClockNumber = tbl_weekly_2011.ClockNumber
WHERE tbl_rate.Person=[Forms]![frm_start]![person_name]
GROUP BY tbl_weekly_2011.ClockNumber, tbl_weekly_2011.WeekNumber, tbl_rate.Person

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Webkins]

I have tried both queries and advice that were so graciously provided by dhookom and PHV and I cannot get either of them to provide the results that I am looking for.

For example: Currently we are in week 36. I have a person who has only worked overtime twice this year. When I run all the above queries I only receive 2 results. I would like to receive 36 results, 1 for each week, whether there is overtime present or not. Overtime not present would be a null value.

Thanks so much for your help.

 

[PHV]

So, you should use a table of week numbers as master table in your query ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886