Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    crosstab property not found (2003/2007)

    I have the following two queries (part of which Hans helped me) that have stopped working. The first which works is:

    SELECT tblAttendance.Present, tblClasses.ClassName, tblStudentsAndClasses.SectionID, tblAttendance.Present, tblSection.txtSection, tblYear.txtYear
    FROM (tblClasses INNER JOIN (tblYear INNER JOIN (tblClassYear INNER JOIN (tblSection INNER JOIN tblStudentsAndClasses ON tblSection.IDSection = tblStudentsAndClasses.SectionID) ON tblClassYear.idClassYear = tblSection.IdClassYear) ON tblYear.YearId = tblClassYear.lnkIdYearClass) ON tblClasses.ClassID = tblClassYear.idClass) INNER JOIN tblAttendance ON tblStudentsAndClasses.StudentClassID = tblAttendance.StudentClassID
    WHERE (((tblYear.txtYear)=[Forms]![frmMAin]![list]
    FROM qerBasCrsTbClassAttendance
    GROUP BY qerBasCrsTbClassAttendance.ClassName, qerBasCrsTbClassAttendance.txtSection
    PIVOT qerBasCrsTbClassAttendance.Present;


    When I try to run it I get a laconic message:

    "Property not found"

    Any ideas
    Thanks
    Zave

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

    Re: crosstab property not found (2003/2007)

    In the first place, tblAttendance.Present occurs twice in qerBasCrsTbClassAttendance. I'd remove the one with the alias Expr1000.

    You'll have to adjust the SQL of the crosstab query to take this into account:

    TRANSFORM Count(qerBasCrsTbClassAttendance.Present) AS CountOfPresent
    SELECT qerBasCrsTbClassAttendance.ClassName, qerBasCrsTbClassAttendance.txtSection, Count(qerBasCrsTbClassAttendance.Present) AS [Total Of Present]
    FROM qerBasCrsTbClassAttendance
    GROUP BY qerBasCrsTbClassAttendance.ClassName, qerBasCrsTbClassAttendance.txtSection
    PIVOT qerBasCrsTbClassAttendance.Present;

    In the second place, declare the parameter [Forms]![frmMAin]![list] in the first column.
    - Select the appropriate data type in the second column.
    - Click OK.
    - Save the query design.

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: crosstab property not found (2003/2007)

    IS there a good overview of when to use the parameters declaration and when not? It drives me crazy sometime you need to declare and sometimes not.
    Thanks

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

    Re: crosstab property not found (2003/2007)

    I've never seen one, but there are two situations in which it is essential:
    1) If you use the parameter directly or indirectly in a crosstab query. Crosstab queries are complicated, and undeclared parameters push Access over the edge.
    2) If you want to open a recordset on the query. DAO doesn't know about parameter prompts or references to forms, so you have to specify the parameter values in code. This requires that the parameters have been declared explicitly.

    If you want to be on the safe side, you should always declare parameters explicitly.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: crosstab property not found (2003/2007)

    Thanks. What is weird is that query was working without the decalration of parameters till today.

Posting Permissions

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