Crosstab query with fixed row headings..possible? (1 Viewer)

butabika-jon

New member
Local time
Today, 12:18
Joined
Apr 24, 2014
Messages
3
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
 

namliam

The Mailman - AWF VIP
Local time
Today, 11:18
Joined
Aug 11, 2003
Messages
11,696
You can predefine / force column names using the IN part of a crosstab query quite easily...

However you cant quite as simple make dummy rows....
You can make a "dummy" table that will contain your required rows and use a union query to fetch both your real data and dummy data to force your rows to be there...
 

butabika-jon

New member
Local time
Today, 12:18
Joined
Apr 24, 2014
Messages
3
Thanks Namliam. I knew there wasn't any crosstab funtional way of doing this, but your suggestion should work. I'll give it a go now. Thanks for the tip
 

namliam

The Mailman - AWF VIP
Local time
Today, 11:18
Joined
Aug 11, 2003
Messages
11,696
One alternative if you want to have fixed row headers, you can actually have a table with your dummy headers and do a left join on that table, instead of working around a union query.
 

theargie

New member
Local time
Today, 07:18
Joined
May 19, 2017
Messages
1
Hi there, this is my first post ever!

I'm trying to accomplish the same but I do not know much SQL, just the basics to understand what an left join is but not to write it myself. I want to accomplish the same thing than the rest of the people in this thread but I would love to receive some help in writing the code. Here's what I've got so far:

TRANSFORM Sum(qryHoursCalculation.Hours) AS HoursSum
SELECT qryHoursCalculation.emp_id, qryHoursCalculation.Name, Sum(qryHoursCalculation.Hours) AS [Total Hours]
FROM qryHoursCalculation
GROUP BY qryHoursCalculation.emp_id, qryHoursCalculation.Name
ORDER BY qryHoursCalculation.Name
PIVOT qryHoursCalculation.CostCenters;

What I would like to achieve is to get all the cost centers to appear even if they are have no values in them.

The cost centers are in another table listed as records in it, and that's why I've used a crosstab query.

Could you help me out? In the meantime thanks a lot for looking into this matter!

Regards,
Pablo.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 10:18
Joined
Jan 14, 2017
Messages
18,186
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
 

Attachments

  • CrosstabReportHeaders1.PNG
    CrosstabReportHeaders1.PNG
    10.2 KB · Views: 2,387
  • LessonAttendanceReportDesign.jpg
    LessonAttendanceReportDesign.jpg
    25.4 KB · Views: 2,305
  • LessonAttendanceReportPreview.jpg
    LessonAttendanceReportPreview.jpg
    25.8 KB · Views: 2,282

Users who are viewing this thread

Top Bottom