crosstab query

[jenske]

hello

i'm working on a human resource database that contains the employees and their skillpoints in different types of skills.

i have an crosstabquery to show the skillpoints per type of skill per employee.

the query is called from a form where you have to select which employees you add and which skills.

everything works fine but people with no skill points assigned or skills with no skillpoints arent shown.

does anyone know how i can also show the empty fields?

thanx a bunch.

 

[jenske]

by showing empty fields i mean showing a skill column even if nobody has ratings for this skill

or showing an employee name row even if the person has no skills listed.

 

[ZmrAbdulla]

While you are in the design veiw of the query right click on the "Column Heading"... bring properties.. type the Column Headings in the order you want them to be displayed.
It is something like
"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"
hope this helps

Zameer Abdulla
_{Jack of Visual Basic Programming, Master in Dining & Sleeping}
Visit Me

 

[GingerR]

The best thing is to have a table/query which has every combination of Employees and Skills listed. Then you'd use this in your crosstab query, with joins on both Employees and Skills, with the join arrows pointing towards your original table. You'd then change the crosstab query grid to show every Employee and Skill from the 'new' table/query instead of the Employee and Skill from your existing table, along with any associated data that exists.

To get this 'new' query, simply make a query based on two tables which you should already have: one which has a unique list of employees, and one which has a unique list of skills. Just put both tables in a new query, do not make any joins between them, and put the Employee (or EmployeeID, whatever you have) and the Skill (skillID?) into the query grid. this way you'll have a list of every Employee+Skill combination available.

This way, if you add new Skills or Employees, your crosstab and any reports built on it will always just update with the new data.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244