Need results for all employees, defined to a specific month

[drennev]

Hello. I'm using a crosstab query to pull results for all employees, and want to reflect errors made in a specific month. I've tried to include a date range in my query, but then the employees with no errors during that time period are excluded. I added "is null" and the query then included those with no errors ever, but still excluded those that did not have an error in the dates specified. How do I get all employees?



Thank you for your help!

[June7]

Did you try joining the crosstab back to the employees table?

[drennev]

Yes. I first created a query that included the manager, employee, and customer service scores, all from their individual, related, tables. Then I created a second query using that query plus adding the error and error date; it is that query I am using for the crosstab.

This is a new database with information as of Jan 1. I'm trying to pull January results. I have employees that had no errors in Jan, but did in Feb. Their customer service score is entered. Still, when putting in the date range for January, they are excluded.

[June7]

The join of crosstab to all employees table should have jointype "Include all records from Employees ...". I don't use crosstabs much but seems that should work.

Only other approach I know gets complicated. Join the data table to a data source that has all employees associated with all months (jointype "Include all records from EmployeeMonths ...", compound join on employee and month fields, then run crosstab on that query. The EmployeeMonths could probably be a query of employees and months tables. The query would include both table without a join. This will cause every employee to associate with every month.

[drennev]

Thank you for your suggestions.

I have the jointype as "Include all records from Employees..." already.

I tried the other approach of making a "month" table and creating a query of employees and months. I included that in my crosstab, filtered the month to January, and got even less results than before. Either I didn't follow what you were saying, or that isn't working.

[drennev]

I think I found a solution. I copied my error table and deleted all but Jan errors, and included that in my crosstab. I now have all employees and their Jan errors . Maybe not the most efficient way, but it appears to work! Thanks again.