Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problem Creating Crosstab Query (Access 2000)

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Problem Creating Crosstab Query (Access 2000)

    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.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem Creating Crosstab Query (Access 2000)

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Problem Creating Crosstab Query (Access 2000)

    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?

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem Creating Crosstab Query (Access 2000)

    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.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Problem Creating Crosstab Query (Access 2000)

    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>post 35485</post#> and <post#=134439>post 134439</post#>. They demonstrate two different approaches to creating reports based on crosstab queries with dynamic column headings. Another example can be found in <post#=248210>post 248210</post#>. Check them out, then come back here, if necessary.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem Creating Crosstab Query (Access 2000)

    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?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Problem Creating Crosstab Query (Access 2000)

    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.

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem Creating Crosstab Query (Access 2000)

    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.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Problem Creating Crosstab Query (Access 2000)

    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]

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem Creating Crosstab Query (Access 2000)

    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

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Problem Creating Crosstab Query (Access 2000)

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

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Problem Creating Crosstab Query (Access 2000)

    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"

  14. #14
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem Creating Crosstab Query (Access 2000)

    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.

  15. #15
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem Creating Crosstab Query (Access 2000)

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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •