Crosstab fields as report group headers

[Anonymous]

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

 

[Duane Hookom]

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

 

[Anonymous]

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

 

[Anonymous]

I'm still plugging away at this without much luck yet... any takers?

Jeff

 

[Alexandria]

CrossTab solution that worked for me

I had similar problems with a crosstab that would have different column names depending on user selection, and could vary anywhere from 1 to 60 columns. The best example was at the site below. Just pay real closs attention to the underlying code and where it actually lists the query names. Look at the "rptReductionByPhysician_CrossTab15" report and modify it to fit your needs. http://www.rogersaccesslibrary.com/download3.asp?SampleName=CrossTabReport.mdb
Hope this helps