Report Based on Crosstab Query

[Guest]

We use a database set up so that each employee can use a form to enter the
time they spend each day on a project. A Crosstab query is then used to
summarize the total time per project per staff member. (Projects are the
rows, staff is the columns, and hours are summed for each). Also, a
parameter is set in the query so that a particular date range can be entered.

Here is my problem, the query is working and giving the approprate totals,
but I can't seem to get the report to work. When I try to set up a report
based on that query, there are no field to choose from. however, if you look
at the query there are 5 fields: Project, Staff, Hours, Total of Hours, &
Date (Between Beginning Date and Ending Date). Sorry if this seems
elementary, but i've never encountered this before and am not sure what to
do. I need to set up a report that looks like this:

Smith Jones Miller Total
Project #1 10 15 5 30
Project #2 20 5 10 35
Project #3 5 10 10 25
etc..

Any help is appreciated. Thanks!

 

[Guest]

Have you:
- enter column headings values into the Column Headings property?
- Have you set the query parameter data types?
- Are you using controls on a form to enter parameters?
If you can't resolve this, come back with the SQL view of your crosstab query.

 

[Guest]

It was the column headings. Once I figured out how to go in and set those, I
now see fields to bring into the report. These crosstabs can be so
confusing! Thanks for your help!

 

[Larry Linson]

It was the column headings. Once I figured out how to go
in and set those, I now see fields to bring into the report.
These crosstabs can be so confusing!

There _is_ a learning curve for Crosstab Queries and Reports, but there are
times (which may be rare or frequent, depending on the kind of databases you
build) that they do with ease what you need, and any alternative method
would require "jumping through flaming hoops".

Larry Linson
Microsoft Access MVP

 

[Bumbino]

Duane, I am having this same problem where there are no fields available when
I try to generate the report. How do I enter column headings values into the
column headings property?