Crosstab Report, Show All Headings

[Techpriest]

Using Access 2000.

I have a database with three tables. One has all the employees,
another has all the classes. Those tables connects to a table called
xTab, this table has fields for Name, Class and Date. Name ties to the
key in the Employee table, Class ties to the key in the Class table.
Date is just the date the class was taken.

To keep life simple let's say I have 10 classes and 20 employees. Each
employee can have 1 or more classes, and no employee has taken a single
class more then once.

I have a query that combines the three tables. It will show every
person and every class they have taken, and the date they took the
class.

I made a CrossTab query from the above query. The rows are employee
names, the columns are the classes. Where they meet is has the latest
date the class was taken.

I made a report based on the CrossTab query. It has all the employees
along the left hand side. Across the top it has 9 classes. Nobody
took the class whose title starts with the word "Risk" so it was not on
the report. I add a dummy employee and give him the class. Run the
report wizard again and now I have 10 classes across the top like I
need. Remove the dummy employee and the report will not run, it errors
on the class Risk. Since we know somebody will eventually take the
class I need it in the report. I need to get all the classes in the
crosstab query, even if nobody took the class yet

Went to the original query that combines the three tables. Changed the
relationship between the xTab table and the Class table to one-to-many,
show all in Class and just those in xTab that match. It tells me it
can't run the query because it contains "ambigious outer joins".

Thanks for any direction.

 

[Dirk Goldgar]

Using Access 2000.

I have a database with three tables. One has all the employees,
another has all the classes. Those tables connects to a table called
xTab, this table has fields for Name, Class and Date. Name ties to
the key in the Employee table, Class ties to the key in the Class
table. Date is just the date the class was taken.

To keep life simple let's say I have 10 classes and 20 employees.
Each employee can have 1 or more classes, and no employee has taken a
single class more then once.

I have a query that combines the three tables. It will show every
person and every class they have taken, and the date they took the
class.

I made a CrossTab query from the above query. The rows are employee
names, the columns are the classes. Where they meet is has the latest
date the class was taken.

I made a report based on the CrossTab query. It has all the employees
along the left hand side. Across the top it has 9 classes. Nobody
took the class whose title starts with the word "Risk" so it was not
on the report. I add a dummy employee and give him the class. Run
the report wizard again and now I have 10 classes across the top like
I need. Remove the dummy employee and the report will not run, it
errors on the class Risk. Since we know somebody will eventually
take the class I need it in the report. I need to get all the
classes in the crosstab query, even if nobody took the class yet

Went to the original query that combines the three tables. Changed
the relationship between the xTab table and the Class table to
one-to-many, show all in Class and just those in xTab that match. It
tells me it can't run the query because it contains "ambigious outer
joins".

Thanks for any direction.

You can specify fixed column headings on the crosstab query's property
sheet.

 

[Douglas J Steele]

Can you create a query that joins the tables appropriate, and then use that
query as the source for your Crosstab?

 

[Techpriest]

Thanks for those that helped.

I was trying to skip a step.

I needed to make a query that joined Class and xTab. The relationship
between the key in Class and Class field in xTab was set to "show all
CLASSES and only xTab records where they matched". That got me ALL the
classes. I named it qClassxTab

I then made a query that combined qClassxTab and the table Employees.
It was set to include ALL from qClassxTab and only those Employees that
matched.

 

[Guest]

You can use a couples of spaces for the dummy employee name. This will
result in a blank line in the output.