Dynamic Data Source for Crosstab Queries in a Report

[iknowkungfu]

I need to generate reports that are based on crosstab queries.

In total, if they were manually created, I would need 180 crosstabs. This is impractical, inflexible and bad design. And insane.

There will be approx 18 different reports with 10 different crosstabs in each. The crosstabs have the same row headings, column headings and data, but will be sourced from different underlying queries. By which I mean the query on which the crosstab is based.

There must be a way to allow different queries or different crosstabs to be specified at runtime. I've found out that apparently you can't specify field-names at runtime without VBA...

I've looked at example VBA code that does something sort of similar (Dynamic Crosstab queries in reports), but it's beyond my understanding.

Failing that, is there some way to automatically write the necessary 180 crosstab queries without doing it the hard way?

 

[iknowkungfu]

To simplify the problem:

I need a system which can report on the numbers of employees, for example, of different races in different job roles. Then another crosstab of numbers of people with diasbilities in different job roles. There are six different fields which need to be put against job roles.

The same then needs to be done but instead of job roles, e.g. employee leaving dates (grouped by month) and so on, in total there are about 13 reports with different items on the y axis, but the same x axes for the set of crosstabs in each report.

Below is a badly tabbed set of examples, also attached in readable form.

The data is only coming from the same set of tables within a report, across the reports it is varying (e.g. Leaving Date and Application for promotion are held in different tables).



Report #1 Diversity Data for Current Employees by Job Role

Race1 Race2 Race3 Race4
JobRole1 1 0 3 4
JobRole2 5 8 4 0
JobRole3 8 6 12 15
JobRole4 2 7 9 6


Disability1 Disability2 Disability3 Disability4
JobRole1 1 0 3 4
JobRole2 5 8 4 0
JobRole3 8 6 12 15
JobRole4 2 7 9 6

........




Report #2 Diversity Data for Current Employees by Date Left (From Jan -- April)

Race1 Race2 Race3 Race4
Jan 1 0 3 4
Feb 5 8 4 0
Mar 8 6 12 15
April 2 7 9 6


Disability1 Disability2 Disability3 Disability4
Jan 1 0 3 4
Feb 5 8 4 0
Mar 8 6 12 15
April 2 7 9 6

........




Report #3 Diversity Data for Current Employees by Applications for Promotion

Race1 Race2 Race3 Race4
Successful 1 0 3 4
Unsuccesful 5 8 4 0


Disability1 Disability2 Disability3 Disability4
Successful 1 0 3 4
Unsuccessful 5 8 4 0

........

 

[iknowkungfu]

Thanks Pat, but I think I'm going to have to give up on this route.

I think I'll try working out how to create a form where the user can specify the query the data comes from and then the fields used in either a pivot table or a crosstab. This would be done from a form. Is it worth me starting a new thread for this? If not, can anyone point me in the right direction?

I want to prevent the user from seeing personally identifiable info, so asking them to just use the pivot table wizard is out of the question.

 

[iknowkungfu]

Thanks Pat, this is really helping me.

If anyone is starting on their first major Access project, I can recommend the Access Bible as a comprehensive reference.

 

[iknowkungfu]

I got an error: "Cannot use the crosstab of a non-fixed column as a subquery." For anyone else doing this, you have to explicitly define the possible column headings in the SQL TRANSFORM statement in RecordSource.

How do I: "grab the appropriate global variable and place it in the report's RecordSource"?

 

[iknowkungfu]

OK. I've managed to do that, I hadn't realised that you needed to declare a Global variable in a normal Module, not in the form module.

I've tried every way I can imagine, but I can't seem to dynamically set the control source of the row headings. i.e. the field selected by the user needs to be used as the control source for the text box identifying each row.

Is there a specific command to do this?

 

[JoeCruse]

Any chance you could make your report header a dynamic crosstab report itself? Then add it into the report you are working on as a subreport, in the report header.

I've had to do something like that on a dynamic crosstab report I built.

 

[iknowkungfu]

Joe, I don't have the foggiest what you're trying to say...

I guessed at the syntax and just put into the open event:

row_identifier.ControlSource = "qry_person_is_employee." & GLB_rowSelected

And it worked.