Query Not Showing All Results

[BradBrad]

Hi All,

 SELECT DISTINCTROW Departments.Director, Sum([Absenteeism 2011].Sick1) AS Sick1, Sum([Absenteeism 2011].Sick2) AS Sick2, Sum([Absenteeism 2011].Sick3) AS Sick3, Sum([Absenteeism 2011].Sick4) AS Sick4, Sum([Absenteeism 2011].Sick5) AS Sick5, Sum([Absenteeism 2011].Sick6) AS Sick6, Sum([Absenteeism 2011].Sick7) AS Sick7, Sum([Absenteeism 2011].Sick8) AS Sick8, Sum([Absenteeism 2011].Sick9) AS Sick9, Sum([Absenteeism 2011].Sick10) AS Sick10, Sum([Absenteeism 2011].Sick11) AS Sick11, Sum([Absenteeism 2011].Sick12) AS Sick12, Sum([Absenteeism 2011].Sick13) AS Sick13, Sum([Absenteeism 2011].Sick14) AS Sick14, Sum([Absenteeism 2011].Sick15) AS Sick15
FROM Departments INNER JOIN [Absenteeism 2011] ON (Departments.[Department Name] = [Absenteeism 2011].Department) AND (Departments.Director = [Absenteeism 2011].Director)
GROUP BY Departments.Director;

Basically, I have 6 directors in my departments table and the query above only gives me the sum of one of the directors in my query. I am trying to sum all of the records with director "x", making sure that the directors is the same as the ones in the departments table.

If you need any more explanation, I'm currently online.

Thanks for your help!

 

[spikepl]

A bit off-topic, but you have 15 kinds of sickies? This doesn't look very normalized

 

[BradBrad]

Hahaha, Not my system..

 

[Galaxiom]

The INNER Join will only give results where the Director is in both tables.

However you have much bigger issues with your data strucure. A table with a Year in the name is note how it is done as are fields like Sick1, Sick2 etc

Absentism should be a table with a field for dates that allows it to cover absentism for an indefinite period. The Sick field should store it s data along with another field that holds SickType (1,2,3 etc)

You really need to sort out this structure before you go any further or you will encounter much worse problems as you get further into the project.

 

[BradBrad]

I was thinking of having the admins just copy and paste the table for the new year.. is there a better way to do it?

 

[BradBrad]

I got it working... I took out the following from the code:

 (Departments.[Department Name] = [Absenteeism 2011].Department)