Problem Creating Crosstab Query (Access 2000)

Topic

New Reply

I’m having trouble creating a Crosstab Query. What I’m trying to do is be able to print a report that lists students names (rows) with fees that the user has selected from a form across the top. I have a form that the user uses to select the school year they want to print the report for. They select this from a drop down box. I then have a query (Category Breakdown Query) that has [Forms]![frmChoose Year]![cmbSelectSchoolYear] in the criteria for the SchoolYearID. When I run this query, it works fine. But when I run the Crosstab Query I created which pull the fields from the Category Breakdown Query, I keep getting an error saying “The Microsoft Jet database engine does not recognize ‘[Forms]![frmChoose Year]![cmbSelectSchoolYear]’ as a valid field name or expression.” I don’t know why I’m getting this message.

I want to be able to choose the year I want to run the report for, then select the fees I want to show on the report. Can I not do this? Is this not possible?

Reply | Quote

Replies

Open the query Category Breakdown Query in design view.
Select Query | Parameters…
In the Parameter column, enter the parameter [Forms]![frmChoose Year]![cmbSelectSchoolYear] exactly as it is used in the Criteria line.
In the Data Type column, enter the correct data type (i.e. the data type of SchoolYearID)
Click OK
Save the query.
See if the crosstab query runs now. If not, repeat the above for the crosstab query itself.

Reply | Quote

Awesome! It works great. Thanks, Hans.

But now how do I make a report from the Crosstab Query? I want it to look like it does in the Crosstab Query. The student names are listed for the rows, and the fees are listed along the top for the headings. When I try to use the Crosstab Query in the Report Wizard, there are no fields listed in that query. I’m a bit confused.

Reply | Quote

Are the fees listed across the top of the crosstab query fixed (or do you want them to be fixed), or will they vary depending on the year selected?

Reply | Quote

Well they will change slightly from year to year, and it also depends what fees you selected on the frmSelectCategories form. The ones you select on that form are what I want across the top of the report. The Crosstab Query lists all the fees that you selected on the frmSelectCategories across the top of the query. One time you print the report, you might want the Elementary, and Agenda fee totals showing. But the next time you print it out, you might want the Elementary, Agenda, Local Levy, and Tech Fee showing on the report. I hope this helps.

Reply | Quote

Welcome to the wonderful world of dynamic crosstab reports. Since the column headings will vary, you can’t just design a report with fixed columns. You will need code to adapt the report to the columns returned by the crosstab query. For example, you might have a look at the databases attached to post 35485 and post 134439. They demonstrate two different approaches to creating reports based on crosstab queries with dynamic column headings. Another example can be found in post 248210. Check them out, then come back here, if necessary.

Reply | Quote

I’ve been going over your sample attachment for your posting 134439. I’m having a bit of trouble. I’m getting a “Compile error: user-defined type not defined”. Why am I getting this? Is it something to do with References in the code?

Reply | Quote

It always helps if you tell us which line is highlighted when you get an error message, but it is probably a DAO declaration. Select Tools | References…, then locate and tick the reference to the Microsoft DAO 3.6 Object Library, and click OK.

Reply | Quote

Okay, the references part is alright, but now I’m getting an error message saying “Too few parameters. Expected 1.” I think this is because of the following code:

‘ Set record source.
RecordSource = “Category2 Breakdown_Crosstab”
‘ Open QueryDef object.
Set qdf = CurrentDb.QueryDefs(“Category2 Breakdown_Crosstab”)
‘ Set query parameters based on values in form.
‘ qdf.Parameters(“Forms![frmEmployeeSales]!txtStartDate”) = Forms![frmEmployeeSales]!txtStartDate
‘ qdf.Parameters(“Forms![frmEmployeeSales]!txtEndDate”) = Forms![frmEmployeeSales]!txtEndDate

I put the last two lines into notes because I just don’t know what to put there.

My first form (frmChoose Year) that opens before running this report is the Category Breakdown query. In that query I have:

StudID
LastName
FirstName
SchoolYearID
SchoolYear
InvID
LineNo
FeeID
FeeName
Amount
Applied
Report (yes/no field)

I have [Forms]![frmChoose Year]![cmbSelectSchoolYear] in the Criteria under SchoolYearID. I also have the same thing under the Query, Parameters.

I then press the Next button on the frmChoose Year, and it opens up the frmSelectCategories form. On that form I use query Category2 Breakdown with the following fields in the query:

LastName
FirstName
FeeName
Applied
Report

I have “Yes” in the Criteria of the Report field. I don’t have it in the Query, Parameters, though. I just don’t know what to put in there? Do I put =yes in the Query, Parameters?

I then have a Category2 Breakdown_Crosstab query which has the following fields from the Category2 Breakdown query:

LastName (Group by)
FirstName (Group by)
FeeName (Group by)
Applied (Sum)
TotalOfApplied: Applied (Sum)

I use the above query for my Crosstab Report.

Reply | Quote

A parameter in a query is value that is either filled in by the user, or comes from a form. A fixed value such as 99, or “New York”, or Yes does not count as a parameter. Your query has one parameter [Forms]![frmChoose Year]![cmbSelectSchoolYear], so you must use that in the code.

Replace the two lines

‘ qdf.Parameters(“Forms![frmEmployeeSales]!txtStartDate”) = Forms![frmEmployeeSales]!txtStartDate
‘ qdf.Parameters(“Forms![frmEmployeeSales]!txtEndDate”) = Forms![frmEmployeeSales]!txtEndDate

by this one (uncommmented):

qdf.Parameters(“[Forms]![frmChoose Year]![cmbSelectSchoolYear]”) = [Forms]![frmChoose Year]![cmbSelectSchoolYear]

Reply | Quote

Oh boy, I’ve changed that one line, now I get an error message saying “The Microsoft Jet database engine does not recognize ” as a valid field name or expression”. What does this mean? Here’s all the code I am using:

Private Sub Report_Open(Cancel As Integer)
Const conNumColumns = 11
Dim qdf As QueryDef
Dim rst As Recordset
Dim intColumnCount As Integer
Dim intX As Integer
Dim strRowTotal As String
Dim strGroupTotal As String
Dim strGrandTotal As String

‘ Don’t open report if frmEmployeeSales is not loaded.
If Not IsLoaded(“frmSelectCategories”) Then
Cancel = True
MsgBox “Please open this report from frmSelectCategories.”, vbExclamation
Exit Sub
End If

On Error GoTo Handle_Err

‘ Set record source.
RecordSource = “Category2 Breakdown_Crosstab”
‘ Open QueryDef object.
Set qdf = CurrentDb.QueryDefs(“Category2 Breakdown_Crosstab”)
‘ Set query parameters based on values in form.
qdf.Parameters(“Forms![frmChoose Year]!cmbSelectSchoolYear”) = Forms![frmChoose Year]![cmbSelectSchoolYear]
‘ qdf.Parameters(“Forms![frmEmployeeSales]!txtEndDate”) = Forms![frmEmployeeSales]!txtEndDate

‘ Open recordset.
Set rst = qdf.OpenRecordset
‘ Don’t open report if there are no data.
If rst.RecordCount = 0 Then
MsgBox “No records found.”, vbInformation
Cancel = True
GoTo Handle_Exit
End If

‘ Fix number of columns in crosstab query and limit to max available.
intColumnCount = rst.Fields.Count – 1
If intColumnCount >= conNumColumns Then
intColumnCount = conNumColumns – 1
End If

‘ Set control source of text box in group footer to first field in crosstab query.
txtGroupName.ControlSource = rst(0).Name

For intX = 1 To intColumnCount
‘ Set caption of label in page header to field name.
Me(“txtHeading” & intX).Caption = rst(intX).Name
‘ Set control source of text box in detail section to field name; replace nulls by 0.
Me(“txtColumn” & intX).ControlSource = “=Nz([” & rst(intX).Name & “], 0)”
Next intX

‘ Start totals in column 2 (the first column with a crosstab value).
For intX = 2 To intColumnCount
‘ Calculate row total.
strRowTotal = strRowTotal & ” + [txtColumn” & intX & “]”
‘ Set control source of text box in group footer to sum of corresponding field; replace nulls by 0.
Me(“txtSubtotal” & intX).ControlSource = “=Nz(Sum([” & rst(intX).Name & “]), 0)”
‘ Calculate row total for group.
strGroupTotal = strGroupTotal & ” + [txtSubtotal” & intX & “]”
‘ Set control source of text box in report footer to sum of corresponding field.
Me(“txtTotal” & intX).ControlSource = “=Sum([” & rst(intX).Name & “])”
‘ Calculate grand total.
strGrandTotal = strGrandTotal & ” + [txtTotal” & intX & “]”
Next intX

‘ Set caption of next available label in page header to “Totals”.
Me(“txtHeading” & (intColumnCount + 1)).Caption = “Totals”
‘ Set control source of next available text box in detail section to row sum.
Me(“txtColumn” & (intColumnCount + 1)).ControlSource = “=” & Mid(strRowTotal, 4)
‘ Set control source of next available text box in group footer to row total for group.
Me(“txtSubtotal” & (intColumnCount + 1)).ControlSource = “=” & Mid(strGroupTotal, 4)
‘ Set control source of next available text box in report footer to grand total.
Me(“txtTotal” & (intColumnCount + 1)).ControlSource = “=” & Mid(strGrandTotal, 4)

DoCmd.Maximize

Handle_Exit:
On Error Resume Next
rst.Close
Set rst = Nothing
Set qdf = Nothing
Exit Sub

Handle_Err:
MsgBox Err.Description, vbExclamation
Resume Handle_Exit
End Sub

Reply | Quote

It means that a value is null (empty) where it shouldn’t be, probably Forms![frmChoose Year]![cmbSelectSchoolYear].

Reply | Quote

Please see my post to Pat, post number 390853 . Still having trouble understanding what it’s doing.

Reply | Quote

Put a rem on the On Error Goto line :
‘ On Error GoTo Handle_Err
Run your report and when the code stops, click Debug and tell us what line give the error. It is the yellow line in the debug window.

Reply | Quote

I don’t get a Debug window. I can’t debug. I just get the Access error box coming up saying “the Microsoft Jet database engine does not recognize ” as a valid field name or expression. Now what?

Reply | Quote

Did you insert an apostrophe ‘ in front of the line

On Error GoTo Handle_Err

as suggested by Francois? The error message should give you a choice Continue | End | Debug | Help then.

Reply | Quote

I did that, but I don’t get all those buttons. I only get OK and HELP. And the Help button doesn’t work.

Reply | Quote

Click in the line Private Sub Report_Open…
Press F9 to set a breakpoint.
Try to open the report from the form.
The code should pause at the line mentioned above.
Each time you press F8, you will execute one instruction. Do this, and keep track of where you are.
On which line does the error occur?

Reply | Quote

Okay, I did what you said and here is what I get. Each time I pressed F8 it went to the next instruction and showed yellow (that’s okay, right?). Then when I finally got down to the last couple of sections of the code, the error box came up when I pressed F8 after the line “Exit Sub” under the Handle_Exit.

Handle_Exit:
On Error Resume Next
rst.Close
Set rst = Nothing
Set qdf = Nothing
Exit Sub

Handle_Err:
MsgBox Err.Description, vbExclamation
Resume Handle_Exit
End Sub

Reply | Quote

The yellow highlighting is as expected.

The rest doesn’t make sense. I don’t think we’re going to solve this from a distance. Could you post a stripped down copy of the database?

• Make a copy of the database and work with that.
• Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
• In the remaining table(s), remove most records – leave only the minimum number necessary to demonstrate the problem.
• Remove or modify data of a confidential nature.
• Do a compact and repair (Tools/Database Utilities).
• Make a zip file containing the database; it should be below 100KB.
• If you have difficulties getting the zip file below 100 KB, save the database in Access 97 format and then zip it.
• Attach the zip file to a reply.
  [/list]That would enable Loungers to look at the problem directly.

Reply | Quote

Here it is. Thanks, Hans.

Reply | Quote

Could you also post a stripped down copy of the backend database, please? I can’t do much in the frontend without any data…

Reply | Quote

Oops! That would help, wouldn’t it?

Reply | Quote

Here is the modified front end. After unzipping it, you will have to relink the tables (Tools | Database Utilities | Linked Table Manager…)

There were several problems, most of them arising because your query doesn’t have the same structure as the sample database you used. This is not your fault, it is a general problem with crosstab reports: you need to adapt them for each situation. Among the things I changed:
– Made DAO declarations explicit.
– Removed grouping level from report, and removed all references to the grouping level from the code
– Made a concatenated “full name” field in the query.

It is not finished yet – at the moment, the query and report ignore the settings of the check boxes on the second form; I just made the report work “as is”. Feel free to post further questions.

Reply | Quote

Well it’s working fine now. Thanks so much.

Reply | Quote

Well it’s working fine now. Thanks so much.

Reply | Quote

Here is the modified front end. After unzipping it, you will have to relink the tables (Tools | Database Utilities | Linked Table Manager…)

There were several problems, most of them arising because your query doesn’t have the same structure as the sample database you used. This is not your fault, it is a general problem with crosstab reports: you need to adapt them for each situation. Among the things I changed:
– Made DAO declarations explicit.
– Removed grouping level from report, and removed all references to the grouping level from the code
– Made a concatenated “full name” field in the query.

It is not finished yet – at the moment, the query and report ignore the settings of the check boxes on the second form; I just made the report work “as is”. Feel free to post further questions.

Reply | Quote

Oops! That would help, wouldn’t it?

Reply | Quote

Could you also post a stripped down copy of the backend database, please? I can’t do much in the frontend without any data…

Reply | Quote

Here it is. Thanks, Hans.

Reply | Quote

The yellow highlighting is as expected.

The rest doesn’t make sense. I don’t think we’re going to solve this from a distance. Could you post a stripped down copy of the database?

• Make a copy of the database and work with that.
• Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
• In the remaining table(s), remove most records – leave only the minimum number necessary to demonstrate the problem.
• Remove or modify data of a confidential nature.
• Do a compact and repair (Tools/Database Utilities).
• Make a zip file containing the database; it should be below 100KB.
• If you have difficulties getting the zip file below 100 KB, save the database in Access 97 format and then zip it.
• Attach the zip file to a reply.
  [/list]That would enable Loungers to look at the problem directly.

Reply | Quote

Okay, I did what you said and here is what I get. Each time I pressed F8 it went to the next instruction and showed yellow (that’s okay, right?). Then when I finally got down to the last couple of sections of the code, the error box came up when I pressed F8 after the line “Exit Sub” under the Handle_Exit.

Handle_Exit:
On Error Resume Next
rst.Close
Set rst = Nothing
Set qdf = Nothing
Exit Sub

Handle_Err:
MsgBox Err.Description, vbExclamation
Resume Handle_Exit
End Sub

Reply | Quote

Click in the line Private Sub Report_Open…
Press F9 to set a breakpoint.
Try to open the report from the form.
The code should pause at the line mentioned above.
Each time you press F8, you will execute one instruction. Do this, and keep track of where you are.
On which line does the error occur?

Reply | Quote

I did that, but I don’t get all those buttons. I only get OK and HELP. And the Help button doesn’t work.

Reply | Quote

Did you insert an apostrophe ‘ in front of the line

On Error GoTo Handle_Err

as suggested by Francois? The error message should give you a choice Continue | End | Debug | Help then.

Reply | Quote

I don’t get a Debug window. I can’t debug. I just get the Access error box coming up saying “the Microsoft Jet database engine does not recognize ” as a valid field name or expression. Now what?

Reply | Quote

Please see my post to Pat, post number 390853 . Still having trouble understanding what it’s doing.

Reply | Quote

It means that a value is null (empty) where it shouldn’t be, probably Forms![frmChoose Year]![cmbSelectSchoolYear].

Reply | Quote

Hi Jen
What is the following line of code meant to do?

RecordSource = “Category2 Breakdown_Crosstab”

If you want to set the RecordSource of the report it should probably be:

Me.RecordSource = “Category2 Breakdown_Crosstab”

Reply | Quote

Hi there, Pat! I tried what you suggested, but it still doesn’t work. I still keep getting the message “The Microsoft Jet database engine does not recognize ” as a valid field name or expression”. I did notice that my crosstab query doesn’t seem to come out right. I tried using an IIF statement to check and see if there was a null value, then put a zero in. My first two queries do that. There is a zero instead of a blank where applicable. But when I get to the Crosstab Query, the first fee which I checked off on the second form–the first form you select the year you want to see, the second form you select what fees you want to see on the form–has zeros where there was a null value. But the second fee that I had checked off, as wanting on the report, still has null values in it. Is this why I’m getting the message? I don’t know how to rectify this. I need to be able to list all the students and put whether they paid any amount or not. So, all the students will have an invoice, but not all will have paid something against their invoice. That is what I want to show on my report.

Reply | Quote

Hi there, Pat! I tried what you suggested, but it still doesn’t work. I still keep getting the message “The Microsoft Jet database engine does not recognize ” as a valid field name or expression”. I did notice that my crosstab query doesn’t seem to come out right. I tried using an IIF statement to check and see if there was a null value, then put a zero in. My first two queries do that. There is a zero instead of a blank where applicable. But when I get to the Crosstab Query, the first fee which I checked off on the second form–the first form you select the year you want to see, the second form you select what fees you want to see on the form–has zeros where there was a null value. But the second fee that I had checked off, as wanting on the report, still has null values in it. Is this why I’m getting the message? I don’t know how to rectify this. I need to be able to list all the students and put whether they paid any amount or not. So, all the students will have an invoice, but not all will have paid something against their invoice. That is what I want to show on my report.

Reply | Quote

Hi Jen
What is the following line of code meant to do?

RecordSource = “Category2 Breakdown_Crosstab”

If you want to set the RecordSource of the report it should probably be:

Me.RecordSource = “Category2 Breakdown_Crosstab”

Reply | Quote

Oh boy, I’ve changed that one line, now I get an error message saying “The Microsoft Jet database engine does not recognize ” as a valid field name or expression”. What does this mean? Here’s all the code I am using:

Private Sub Report_Open(Cancel As Integer)
Const conNumColumns = 11
Dim qdf As QueryDef
Dim rst As Recordset
Dim intColumnCount As Integer
Dim intX As Integer
Dim strRowTotal As String
Dim strGroupTotal As String
Dim strGrandTotal As String

‘ Don’t open report if frmEmployeeSales is not loaded.
If Not IsLoaded(“frmSelectCategories”) Then
Cancel = True
MsgBox “Please open this report from frmSelectCategories.”, vbExclamation
Exit Sub
End If

On Error GoTo Handle_Err

‘ Set record source.
RecordSource = “Category2 Breakdown_Crosstab”
‘ Open QueryDef object.
Set qdf = CurrentDb.QueryDefs(“Category2 Breakdown_Crosstab”)
‘ Set query parameters based on values in form.
qdf.Parameters(“Forms![frmChoose Year]!cmbSelectSchoolYear”) = Forms![frmChoose Year]![cmbSelectSchoolYear]
‘ qdf.Parameters(“Forms![frmEmployeeSales]!txtEndDate”) = Forms![frmEmployeeSales]!txtEndDate

‘ Open recordset.
Set rst = qdf.OpenRecordset
‘ Don’t open report if there are no data.
If rst.RecordCount = 0 Then
MsgBox “No records found.”, vbInformation
Cancel = True
GoTo Handle_Exit
End If

‘ Fix number of columns in crosstab query and limit to max available.
intColumnCount = rst.Fields.Count – 1
If intColumnCount >= conNumColumns Then
intColumnCount = conNumColumns – 1
End If

‘ Set control source of text box in group footer to first field in crosstab query.
txtGroupName.ControlSource = rst(0).Name

For intX = 1 To intColumnCount
‘ Set caption of label in page header to field name.
Me(“txtHeading” & intX).Caption = rst(intX).Name
‘ Set control source of text box in detail section to field name; replace nulls by 0.
Me(“txtColumn” & intX).ControlSource = “=Nz([” & rst(intX).Name & “], 0)”
Next intX

‘ Start totals in column 2 (the first column with a crosstab value).
For intX = 2 To intColumnCount
‘ Calculate row total.
strRowTotal = strRowTotal & ” + [txtColumn” & intX & “]”
‘ Set control source of text box in group footer to sum of corresponding field; replace nulls by 0.
Me(“txtSubtotal” & intX).ControlSource = “=Nz(Sum([” & rst(intX).Name & “]), 0)”
‘ Calculate row total for group.
strGroupTotal = strGroupTotal & ” + [txtSubtotal” & intX & “]”
‘ Set control source of text box in report footer to sum of corresponding field.
Me(“txtTotal” & intX).ControlSource = “=Sum([” & rst(intX).Name & “])”
‘ Calculate grand total.
strGrandTotal = strGrandTotal & ” + [txtTotal” & intX & “]”
Next intX

‘ Set caption of next available label in page header to “Totals”.
Me(“txtHeading” & (intColumnCount + 1)).Caption = “Totals”
‘ Set control source of next available text box in detail section to row sum.
Me(“txtColumn” & (intColumnCount + 1)).ControlSource = “=” & Mid(strRowTotal, 4)
‘ Set control source of next available text box in group footer to row total for group.
Me(“txtSubtotal” & (intColumnCount + 1)).ControlSource = “=” & Mid(strGroupTotal, 4)
‘ Set control source of next available text box in report footer to grand total.
Me(“txtTotal” & (intColumnCount + 1)).ControlSource = “=” & Mid(strGrandTotal, 4)

DoCmd.Maximize

Handle_Exit:
On Error Resume Next
rst.Close
Set rst = Nothing
Set qdf = Nothing
Exit Sub

Handle_Err:
MsgBox Err.Description, vbExclamation
Resume Handle_Exit
End Sub

Reply | Quote

A parameter in a query is value that is either filled in by the user, or comes from a form. A fixed value such as 99, or “New York”, or Yes does not count as a parameter. Your query has one parameter [Forms]![frmChoose Year]![cmbSelectSchoolYear], so you must use that in the code.

Replace the two lines

‘ qdf.Parameters(“Forms![frmEmployeeSales]!txtStartDate”) = Forms![frmEmployeeSales]!txtStartDate
‘ qdf.Parameters(“Forms![frmEmployeeSales]!txtEndDate”) = Forms![frmEmployeeSales]!txtEndDate

by this one (uncommmented):

qdf.Parameters(“[Forms]![frmChoose Year]![cmbSelectSchoolYear]”) = [Forms]![frmChoose Year]![cmbSelectSchoolYear]

Reply | Quote

Okay, the references part is alright, but now I’m getting an error message saying “Too few parameters. Expected 1.” I think this is because of the following code:

‘ Set record source.
RecordSource = “Category2 Breakdown_Crosstab”
‘ Open QueryDef object.
Set qdf = CurrentDb.QueryDefs(“Category2 Breakdown_Crosstab”)
‘ Set query parameters based on values in form.
‘ qdf.Parameters(“Forms![frmEmployeeSales]!txtStartDate”) = Forms![frmEmployeeSales]!txtStartDate
‘ qdf.Parameters(“Forms![frmEmployeeSales]!txtEndDate”) = Forms![frmEmployeeSales]!txtEndDate

I put the last two lines into notes because I just don’t know what to put there.

My first form (frmChoose Year) that opens before running this report is the Category Breakdown query. In that query I have:

StudID
LastName
FirstName
SchoolYearID
SchoolYear
InvID
LineNo
FeeID
FeeName
Amount
Applied
Report (yes/no field)

I have [Forms]![frmChoose Year]![cmbSelectSchoolYear] in the Criteria under SchoolYearID. I also have the same thing under the Query, Parameters.

I then press the Next button on the frmChoose Year, and it opens up the frmSelectCategories form. On that form I use query Category2 Breakdown with the following fields in the query:

LastName
FirstName
FeeName
Applied
Report

I have “Yes” in the Criteria of the Report field. I don’t have it in the Query, Parameters, though. I just don’t know what to put in there? Do I put =yes in the Query, Parameters?

I then have a Category2 Breakdown_Crosstab query which has the following fields from the Category2 Breakdown query:

LastName (Group by)
FirstName (Group by)
FeeName (Group by)
Applied (Sum)
TotalOfApplied: Applied (Sum)

I use the above query for my Crosstab Report.

Reply | Quote

It always helps if you tell us which line is highlighted when you get an error message, but it is probably a DAO declaration. Select Tools | References…, then locate and tick the reference to the Microsoft DAO 3.6 Object Library, and click OK.

Reply | Quote

I’ve been going over your sample attachment for your posting 134439. I’m having a bit of trouble. I’m getting a “Compile error: user-defined type not defined”. Why am I getting this? Is it something to do with References in the code?

Reply | Quote

Welcome to the wonderful world of dynamic crosstab reports. Since the column headings will vary, you can’t just design a report with fixed columns. You will need code to adapt the report to the columns returned by the crosstab query. For example, you might have a look at the databases attached to post 35485 and post 134439. They demonstrate two different approaches to creating reports based on crosstab queries with dynamic column headings. Another example can be found in post 248210. Check them out, then come back here, if necessary.

Reply | Quote

Well they will change slightly from year to year, and it also depends what fees you selected on the frmSelectCategories form. The ones you select on that form are what I want across the top of the report. The Crosstab Query lists all the fees that you selected on the frmSelectCategories across the top of the query. One time you print the report, you might want the Elementary, and Agenda fee totals showing. But the next time you print it out, you might want the Elementary, Agenda, Local Levy, and Tech Fee showing on the report. I hope this helps.

Reply | Quote

Are the fees listed across the top of the crosstab query fixed (or do you want them to be fixed), or will they vary depending on the year selected?

Reply | Quote

Awesome! It works great. Thanks, Hans.

But now how do I make a report from the Crosstab Query? I want it to look like it does in the Crosstab Query. The student names are listed for the rows, and the fees are listed along the top for the headings. When I try to use the Crosstab Query in the Report Wizard, there are no fields listed in that query. I’m a bit confused.

Reply | Quote