Query is skipping values

[2709236]

Hi,

I have a simple query.

It pulls production hours, by employee number, by week. However, if for whatever reason an employee gets missed by production, I need the hours for that employee to show up as 'zero' for that week, instead of not listing the week at all.

To accomplish this I setup a table that lists every employee number by every week. In my query I create a relationship between the production data and the table listing the weeks (read: I've joined the 'WEEK' field between each) hoping to have the following result:

Wk 35 Emp #1 40 hours
Wk 36 Emp #1 45 hours

Instead I'm still only getting Wk 35 showing up.

What could be causing this miss? What can I provide for you to better understand my issue? (new to Access, not sure what you need to see).

Thanks

[2709236]

Posting the SQL view. Note that "PERNR" is the employee number.

SELECT [SM EE Direct Hours].PERNR, WK01.WEEK, Sum([SM EE Direct Hours].HOURS) AS SumOfHOURS
FROM WK01 LEFT JOIN [SM EE Direct Hours] ON WK01.WEEK = [SM EE Direct Hours].WEEK
GROUP BY [SM EE Direct Hours].PERNR, WK01.WEEK;

[davegugg]

I believe you should set up your database with three tables: an employee table, a week table (this one would be quite simple), and a table that holds the hourly data. The employee ID should be the primary key in the employee table, the week ID should be the primary key in the Week table, and the hourly table should have a generic ID. All hourly data should be kept in the Hourly table.

Can you post sample database?