Crosstab fields as report group headers

Simple Question:

I have a crosstab query. Is there a way to get Access 2000 to use these
varying fields in a report? Or is there a way to use a crosstab's field
names as groups or headers?

Long-winded Explanation:

I collect data in this fashion:

Clock# StartDate EndDate AbsenceType Dept Classification

7 01/01/06 01/04/06 Vacation Ship. Clerk
13 01/02/06 01/02/06 LOA I.T. Slug
35 01/04/06 01/05/06 Misc. I.T. Dweeb

I expand the above data as such (using a query):

ActiveDate Clock# AbsenceType

01/01/06 7 Vacation
01/02/06 7 Vacation
01/02/06 13 LOA
01/03/06 7 Vacation
01/04/06 7 Vacation
01/04/06 35 Misc.
01/05/06 35 Misc.

I use a crosstab query on the above query to create this:

ActiveDate 7 13 35

01/01/06 V
01/02/06 V L
01/03/06 V
01/04/06 V M
01/05/06 M

So I'm trying to get a report to show the crosstab's data in the same way,
but reports and the controls on them need set fields to bind to. Therein
lies the problem. The 'ActiveDate' column wouldn't change, but the
Clock#'s constantly do depending on what criteria is chosen, such as what
Dept and/or Classification.

End Goal:

SOMEHOW, whether using a crosstab query or not, get a report that shows
'Clock#' as Column Headings (Clock#'s vary), each date in a specified
date-range (I'm using 365 dates for 2006) as Row Headings, and some other
data as Values ('AbsenceType', in the example above).

It doesn't even technically HAVE to be a report, but I can't figure out
groupings that work this way for Forms...

I've been working on this aspect of my database for 2 weeks, trying
various techniques, but can't seem to find a lead from
support.microsoft.com.

Thanks for looking.

Jeff

You can enter all possible clock# values into the Column Headings property
of the crosstab or review the Crosstab.mdb sample database at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4

--
Duane Hookom
MS Access MVP
--

"Anonymous" wrote in message
newsp.s6twssqqaq8s7f@kvitjb...
Simple Question:

I have a crosstab query. Is there a way to get Access 2000 to use these
varying fields in a report? Or is there a way to use a crosstab's field
names as groups or headers?

Long-winded Explanation:

I collect data in this fashion:

Clock# StartDate EndDate AbsenceType Dept Classification

7 01/01/06 01/04/06 Vacation Ship. Clerk
13 01/02/06 01/02/06 LOA I.T. Slug
35 01/04/06 01/05/06 Misc. I.T. Dweeb

I expand the above data as such (using a query):

ActiveDate Clock# AbsenceType

01/01/06 7 Vacation
01/02/06 7 Vacation
01/02/06 13 LOA
01/03/06 7 Vacation
01/04/06 7 Vacation
01/04/06 35 Misc.
01/05/06 35 Misc.

I use a crosstab query on the above query to create this:

ActiveDate 7 13 35

01/01/06 V
01/02/06 V L
01/03/06 V
01/04/06 V M
01/05/06 M

So I'm trying to get a report to show the crosstab's data in the same way,
but reports and the controls on them need set fields to bind to. Therein
lies the problem. The 'ActiveDate' column wouldn't change, but the
Clock#'s constantly do depending on what criteria is chosen, such as what
Dept and/or Classification.

End Goal:

SOMEHOW, whether using a crosstab query or not, get a report that shows
'Clock#' as Column Headings (Clock#'s vary), each date in a specified
date-range (I'm using 365 dates for 2006) as Row Headings, and some other
data as Values ('AbsenceType', in the example above).

It doesn't even technically HAVE to be a report, but I can't figure out
groupings that work this way for Forms...

I've been working on this aspect of my database for 2 weeks, trying
various techniques, but can't seem to find a lead from
support.microsoft.com.

Thanks for looking.

Jeff

Very interesting sample MDB's...!

It doesn't answer my question but it does lead me in a new direction I
hadn't thought of: a subreport linking ActiveDate and Clock#...

Meanwhile if anyone has another suggestion...

Thanks!

Jeff


On Wed, 22 Mar 2006 14:52:21 -0500, Duane Hookom
wrote:

You can enter all possible clock# values into the Column Headings
property
of the crosstab or review the Crosstab.mdb sample database at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4