Crosstab query with no existing entry

[Omenphaux]

Hi,


I am relatively new to access and am having a problem with crosstab query. I am creating an overtime database to track which area areas of the workplace require the most overtime hours

I have a form where you enter the start and end date and select which type/position of employee you want to look up (ie, lab attendant or technicians)

I have a table with data that has the employees number,name,positions,OTday,overtimearea,overtimer eason,totalhours. The cross tab is set up to have the overtimearea vs overtimereason with the totalhours as the values

the crosstab works until i pick a employee position that does not have an entry for one of the overtime areas (my overtime area is a list of,Lab1,Lab2,Lab3etc) if I pick lab attendant as the position and they have no overtiem in Lab1 i will get an error "the microsoft access database engine does not recognize " as a valid field name or expression"

the crosstab will also not work if i pick a date range that does not have all the overtime areas within that range.

so in the end the crosstab works untill it looks for a column heading or row heading that has no data (or so i think)

Please advise

[June7]

Want to provide project for analsysis? Follow instructions at bottom of my post.