Results 1 to 13 of 13
  1. #1
    Star Lounger
    Join Date
    Jan 2002
    Location
    Massachusetts, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report Layout (2002 (XP))

    I'm going to try and explain this. There are fields from a query and I want them to display on the horizontal of a report like column headings. It seems like a report is a vertical device not a horizontal one. What I am making is an attendance list with class dates (From a query probably 3-10 of em) across the top and student names down the left edge. Any help appreciated

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

    Re: Report Layout (2002 (XP))

    Would you post the Query and we can have a look at it?
    Maybe a Crosstab query is all that is required.
    Pat

  3. #3
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Layout (2002 (XP))

    You might want to take a look at this thread <!post=School Attendances App,20661>School Attendances App<!/post>
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  4. #4
    Star Lounger
    Join Date
    Jan 2002
    Location
    Massachusetts, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Layout (2002 (XP))

    Here is the SQL

    SELECT tblClass.lngClassNumber, tblClass.strClassName, tblClass.dtmStartDate, tblClass.dtmEndDate, tblClass.dtmStartTime, tblClass.dtmEndTime, tblInstructor.strLastName, tblInstructor.strFirstName, tblStudents.strLastName, tblStudents.strFirstName
    FROM tblStudents INNER JOIN ((tblInstructor INNER JOIN tblClass ON tblInstructor.lngInstructorNumber = tblClass.lngInstructor) INNER JOIN tblRegister ON tblClass.lngClassNumber = tblRegister.lngClassNumber) ON tblStudents.lngStudentNumber = tblRegister.lngStudent
    WHERE (((tblClass.lngClassNumber)=[Forms]![frmReportsClass]![Combo7]));

    Combo7 is the number of the class that the list is generated from, It will be an attendence sheet. what I want is rows of students and columns of class dates that reside in a table called tblClassSchedule I have them just displayed on the report via a simple query bringing up the class dates based on the selected class number. I know how to make a crosstab query but I can never get the criteria for just the class I want. Here is the sql for a crosstab I was trying

    TRANSFORM qryCrosstabAtendence.strClassName
    SELECT qryCrosstabAtendence.Student
    FROM qryCrosstabAtendence
    GROUP BY qryCrosstabAtendence.lngClass, qryCrosstabAtendence.Student
    PIVOT qryCrosstabAtendence.dtmClassDate;

    I know I'm over my head on this one. Thanks for any help

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

    Re: Report Layout (2002 (XP))

    Tell me, is the Crosstab query based upon the SQL you have provided?
    There seems to be conflicting names if this is the case, eg qryCrosstabAtendence.Student.
    It would be easier if you were to post the DB so loungers could have a look and probably help you solve it.
    What seems to be wrong with the Crosstab query? What error is it giving?
    Pat

  6. #6
    Star Lounger
    Join Date
    Jan 2002
    Location
    Massachusetts, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Layout (2002 (XP))

    Patt, Ok based on what you told me I created this crosstab that works if I manually put the class # into the criteria. However when I try and but a reference to a field on a form (as illustrated) it gives me a jet database error. Could I just have the reference typed wrong. Thanks for all your help with this.

    TRANSFORM Min(qryAttendenceCross.lngClassNumber) AS MinOflngClassNumber
    SELECT qryAttendenceCross.strLastName
    FROM qryAttendenceCross
    WHERE (((qryAttendenceCross.lngClassNumber)=[Forms]![frmReportsClass]![Combo7])) ##If I remove this and type in say 47. it works fine for class 47##
    GROUP BY qryAttendenceCross.strLastName, qryAttendenceCross.lngClassNumber
    PIVOT qryAttendenceCross.dtmClassDate;

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

    Re: Report Layout (2002 (XP))

    What is the Error that Access throws up at you?
    Pat

  8. #8
    Star Lounger
    Join Date
    Jan 2002
    Location
    Massachusetts, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Layout (2002 (XP))

    The Microsoft Jet Database Engine does not recognize 'Forms!frmRerortsClass![Combo7]' as a valid field name or expression

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

    Re: Report Layout (2002 (XP))

    Is your expression
    'Forms!frmRerortsClass![Combo7]
    or should it be
    'Forms!frmReportsClass![Combo7]

    If it's not that then you may need to use a CreateQueryDef based upon an SQL you have constructed.

    HTH
    Pat

  10. #10
    Star Lounger
    Join Date
    Jan 2002
    Location
    Massachusetts, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Layout (2002 (XP))

    No I typed it wrong in the message both the form and the SQL are named the same.
    I'm not sure what you mean by a CreateQueryDef. are they hard to make and where do you do it

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

    Re: Report Layout (2002 (XP))

    You need to use VBA code just prior to the DoCmd.OpenReport command.

    What you can do is to define your query (named say qryAttendanceCrosstab, this is also the source for your report) as:

    TRANSFORM Min(qryAttendenceCross.lngClassNumber) AS MinOflngClassNumber
    SELECT qryAttendenceCross.strLastName
    FROM qryAttendenceCross
    WHERE (((qryAttendenceCross.lngClassNumber)=47))
    GROUP BY qryAttendenceCross.strLastName, qryAttendenceCross.lngClassNumber
    PIVOT qryAttendenceCross.dtmClassDate;

    Now in VBA code just prior to the OpenReport command put the following:
    Dim strSQL as string
    strSQL = "SELECT qryAttendenceCross.strLastName FROM qryAttendenceCross"
    strSQL = strSQL & " FROM qryAttendenceCross"
    strSQL = strSQL & " WHERE (((qryAttendenceCross.lngClassNumber)=" & [Forms]![frmReportsClass]![Combo7] & ")) "
    strSQL = strSQL & " GROUP BY qryAttendenceCross.strLastName, qryAttendenceCross.lngClassNumber"
    strSQL = strSQL & " PIVOT qryAttendenceCross.dtmClassDate;"
    Dim dbs as DAO.Database, DAO.qdf as QueryDef
    Set dbs = CurrentDB
    set qdf = dbs.CreateQueryDef("qryAttendanceCrosstab", strSQL)
    Set dbs = Nothing
    Set qdf = Nothing
    DoCmd.OpenReport ...............

    HTH
    Pat

  12. #12
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Layout (2002 (XP))

    Q209778 KB article covers this one.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  13. #13
    Star Lounger
    Join Date
    Jan 2002
    Location
    Massachusetts, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Layout (2002 (XP))

    Steve,
    OK the MS fix worked. Here is the new SQL

    PARAMETERS [Forms]![frmReportsClass]![Combo7] Long;
    TRANSFORM StDev(qryCrossAttend.lngClassNumber) AS StDevOflngClassNumber
    SELECT qryCrossAttend.Name
    FROM qryCrossAttend
    WHERE (((qryCrossAttend.lngClassNumber)=[Forms]![frmReportsClass]![Combo7]))
    GROUP BY qryCrossAttend.Name, qryCrossAttend.lngClassNumber
    PIVOT qryCrossAttend.dtmClassDate;

    I made a form out of it and then created a subform in a report. Now I get an error in the report about not being able to use a pass-through or Fixed column width query. It says to define the Column heading in query property but that just causes another error when I run the query. Thanks for the help

Posting Permissions

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