Results 1 to 2 of 2
  1. #1
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051

    Angry How to put a crosstab query on a subform

    I have a crosstab query which basically tells me every pupils test results with the tests being the column heading. There are anywhere between 0 (until their first test) and 18 tests



    I'm trying to get this into a subform with the tblStudent (name etc) so that I can see the progress they are making but am running into wall after wall.

    Firstly, the crosstab

    Code:
    TRANSFORM First(qrydoesthiswork.grade) AS FirstOfgrade
    SELECT qrydoesthiswork.StudentID_PK, qrydoesthiswork.Firstname, qrydoesthiswork.surname, First(qrydoesthiswork.grade) AS [Total Of grade]
    FROM qrydoesthiswork
    GROUP BY qrydoesthiswork.StudentID_PK, qrydoesthiswork.Firstname, qrydoesthiswork.surname
    PIVOT qrydoesthiswork.slotID_FK;
    All fine

    But when I try to put this, or any form based on this, into a subform it tells me I need to set the column headings property. I cant change this as the columns are labelled according to their slotID and this increases every term.


    I've tried reversing the forms so that the main form is the crosstab and the student details is the subform.

    This works Ok unless a student hasn't got a result. Then I get an error "you entered an expression which has no value" in this dlookup.

    Code:
    myCS = Nz(DLookup("slotID_PK", "tblcalendar", "slot like 'christ*' and [yeargp] = " & Me!FrmStudent.Form![yeargp]), "")
    Presumably because there is no student info to link the master and child fields

    Getting stressed!!!

    Can anyone help.

    I'll happily post further details

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Building a stable form or report based on CROSSTAB query is not easy because of the dynamic nature of the column headings. Frankly, I've never heard of anyone trying to use a CROSSTAB on a form, much less a dependent subform. A report based on CROSSTAB is difficult enough. Consider myself lucky I've never needed CROSSTAB.

    If you want to make sure there is a record in the CROSSTAB for every student, then need to include Students table in the CROSSTAB or in qrydoesthiswork with a JOIN that 'includes all Students records ...'. This should result in a column with header [<>] if any student does not have any grades.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 05-11-2017, 07:54 AM
  2. Replies: 2
    Last Post: 04-30-2014, 03:01 PM
  3. Replies: 2
    Last Post: 08-16-2013, 01:36 AM
  4. Crosstab not accepted as subform
    By gazzieh in forum Forms
    Replies: 2
    Last Post: 06-27-2011, 01:49 AM
  5. Replies: 1
    Last Post: 07-30-2010, 10:28 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums