Crosstab Query to include all records from two tables/querys to count no of records.

[venus]

Hai fiends. i have three tables
1)TaskType tbl with fields TaskTypeID(PK), TaskType
2) Places tbl with fields PlaceID(PK), Place
3)Task tbl with fields TaskNo(PK), TaskTypeID(FK), placeID(FK), Task etc.

I want to create a crosstab query to count all records in table3, TaskType as column heading and Place as row heading, query should display all records from TaskType and place tbls. count should show as 0 if no task is done at place and TaskType.


Help me pls.

Thank you in advance.

[June7]

If I understand your requirements, I think you need a dataset of all possible TaskType and Place pairs. This can be generated with a query that includes both TaskTypes and Places tables but without a join clause. This will be a Cartesian relation where every record in each table will join with every record in other table.

Then use that query in another query that joins with the Task table, join type "Include all records from {first query} and only those from Task that match". Do a compound join on the TaskTypeID and PlaceID fields.

Then use that second query as the source for a CROSSTAB.

[venus]

Thank you its working.But, crosstab query showing first row all zeros and first column heading blank. How can i remove this first row.

[June7]

Maybe apply a filter that excludes all records where that field Is Null?

[venus]

Thank you again.