Access World Forums

Access World Forums (https://www.access-programmers.co.uk/forums/index.php)
-   Queries (https://www.access-programmers.co.uk/forums/forumdisplay.php?f=8)
-   -   Crosstab query with fixed row headings..possible? (https://www.access-programmers.co.uk/forums/showthread.php?t=263759)

butabika-jon 04-23-2014 11:31 PM

Crosstab query with fixed row headings..possible?
 
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 04-23-2014 11:59 PM

Re: Crosstab query with fixed row headings..possible?
 
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 04-24-2014 11:09 AM

Re: Crosstab query with fixed row headings..possible?
 
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 04-24-2014 10:17 PM

Re: Crosstab query with fixed row headings..possible?
 
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 05-18-2017 03:51 PM

Re: Crosstab query with fixed row headings..possible?
 
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.

ridders 05-19-2017 03:50 PM

Re: Crosstab query with fixed row headings..possible?
 
3 Attachment(s)
Quote:

Originally Posted by butabika-jon (Post 1346143)
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.

https://www.access-programmers.co.uk...1&d=1495237373

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:
https://www.access-programmers.co.uk...1&d=1495237626

Preview:
https://www.access-programmers.co.uk...1&d=1495237626

Note the predefined but blank columns in both reports

Let me know if you would like any further info


All times are GMT -8. The time now is 10:12 PM.

Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World