View Single Post
Old 05-19-2017, 03:50 PM   #6
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 2,889
Thanks: 52
Thanked 680 Times in 628 Posts
ridders has a spectacular aura about ridders has a spectacular aura about
Re: Crosstab query with fixed row headings..possible?

Quote:
Originally Posted by butabika-jon View Post
Hi
This is my first post ever.
I've setup some crosstab queries with good success and set column headings that appear with blank spaces when there's no data as expected, but I have 1 report that is formatted with set row headings (its a specifically formatted report that i need to produce, that i can't just change).
So I need to basically do the same but with the row headings set (I have got the crosstab working, just not showing the rows with no data as yet).
I have searched on here, but can't find anything that helps, so I presume its either impossible or very hard with a load of code outside of the normal crosstab functionality.
Any help much appreciated...its for a good cause.
Thanks
Jon
Its not that difficult.
I use 2 different approaches:

You can set the column headings in the crosstab query properties ...
e.g. MonAM; MonPM; TuesAM etc
Then use this to set the report columns e.g.



OR ... you can create a 'dynamic crosstab' report where the column headings are defined in the report open event e.g.

Code:
Private Sub Report_Open(Cancel As Integer)

On Error GoTo Err_Handler
    
  '===============================================
    'Dynamic report designed to be used with Crosstab Queries
    
    'This code is especially "tuned" for crosstab queries.
    'I like to have control over the layout, so the lay-out is designed first with "coded" controls.
    'Then the dynamic filling becomes very easy. The raw text I use to help with this is:
    
    'Making the column header and detail data flexible is possible,
    'but needs some VBA code in the OpenReport event.
    
    'To start doing this you need to place the fields "coded" in the report.
    'The column headings could be called "lblCol1", "lblCol2", "lblCol3", etc.
    'The "detail" fields could be called "txtCol1", "txtCol2", "txtCol3", etc.
    
    'The report query has two row header columns and a Total column,
    'therefore the first field is effectively column 4 (count starts at 0 so I used intI=3)
    'but this will vary depending on the report
    
    'Make sure that the number of Columns is not bigger than the number placed.
    'The program code has no protection against that.
     '===============================================

    Dim intI As Integer
    Dim Rs As DAO.Recordset

 'get data source
    strSQL1 = "TRANSFORM First(qryClassLessonAttendance.Mark) AS FirstOfMark" & _
        " SELECT qryClassLessonAttendance.PupilID, qryClassLessonAttendance.Surname, qryClassLessonAttendance.Forename," & _
        " qryClassLessonAttendance.TG, qryClassLessonAttendance.ClassID" & _
        " FROM qryMostRecentLessonsSelectedClassTeacher INNER JOIN (qryClassLessonAttendance INNER JOIN Classes" & _
        " ON qryClassLessonAttendance.ClassID = Classes.ClassID)" & _
        " ON (qryMostRecentLessonsSelectedClassTeacher.TeacherID = qryClassLessonAttendance.TeacherID)" & _
        " AND (qryMostRecentLessonsSelectedClassTeacher.Date = qryClassLessonAttendance.Date)" & _
        " AND (qryMostRecentLessonsSelectedClassTeacher.Period = qryClassLessonAttendance.Period)" & _
        " AND (qryMostRecentLessonsSelectedClassTeacher.ClassID = qryClassLessonAttendance.ClassID)" & _
        " WHERE(((qryClassLessonAttendance.ClassID) = GetClass()))" & _
        " GROUP BY qryClassLessonAttendance.PupilID, qryClassLessonAttendance.Surname, qryClassLessonAttendance.Forename," & _
        " qryClassLessonAttendance.TG, qryClassLessonAttendance.ClassID" & _
        " ORDER BY qryClassLessonAttendance.Surname, qryClassLessonAttendance.Forename, qryClassLessonAttendance.LessonInfo" & _
        " PIVOT qryClassLessonAttendance.LessonInfo;"
        
    Me.LblDates.Caption = DMin("Date", "qryMostRecentLessonsSelectedClassTeacher") & " - " & DMax("Date", "qryMostRecentLessonsSelectedClassTeacher")
    Me.LblFooter.Caption = "NOTE: Attendance marks are shown for the last 40 lessons ONLY. Attendance data is not available for guest pupils. See next page for attendance codes"


    'Debug.Print Me.LblDates.Caption
    'Debug.Print strSQL1
    
    Set Rs = CurrentDb.OpenRecordset(strSQL1)
 
     'Place headers - start at column 4
     
     For intI = 5 To Rs.Fields.Count - 1
        'Me("Lesson" & intI - 4).Caption = Rs.Fields(intI).Name
        
        'Lesson caption based on Rs.Fields(intI).Name
        'This starts with CDbl(Date) & Period (for correct sorting by date & period) then a "#"
        'Display all text after the "#"
         Me("Lesson" & intI - 4).Caption = Mid(Rs.Fields(intI).Name, InStr(Rs.Fields(intI).Name, "#") + 1)
         Me("Lesson" & intI - 4).visible = True
           
     Next intI
 
     'Place controls
     For intI = 5 To Rs.Fields.Count - 1
         Me("Mark" & intI - 4).ControlSource = Rs.Fields(intI).Name
         Me("Mark" & intI - 4).visible = True
     Next intI
 
     'Place Total field
    'Me.ColTotal.ControlSource = "=SUM([" & rs.Fields(2).Name & "])"
    
Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & err.Number & " in Report_Open procedure: " 7 err.Description
    Resume Exit_Handler
    
End Sub
Report design:


Preview:


Note the predefined but blank columns in both reports

Let me know if you would like any further info
Attached Images
File Type: png CrosstabReportHeaders1.PNG (10.2 KB, 445 views)
File Type: jpg LessonAttendanceReportDesign.jpg (25.4 KB, 422 views)
File Type: jpg LessonAttendanceReportPreview.jpg (25.8 KB, 415 views)
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
, Tapatalk


If this answer has helped, please click the Thanks button and/or click the 'reputation' scales symbol on the left and leave a comment. Cheers!
ridders is offline   Reply With Quote