Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-23-2014, 11:31 PM   #1
butabika-jon
Newly Registered User
 
Join Date: Apr 2014
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
butabika-jon is on a distinguished road
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

butabika-jon is offline   Reply With Quote
Old 04-23-2014, 11:59 PM   #2
namliam
The Mailman - AWF VIP
 
Join Date: Aug 2003
Location: Amsterdam/The Netherlands
Posts: 11,388
Thanks: 0
Thanked 797 Times in 784 Posts
namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light
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...
__________________
ruoY yppah namliaM si syawla yppah ot pleh
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.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Remember to use the scales (Bottom left) or the thumb (bottom right)

Always looking for new challenges (Amsterdam Area)
namliam is offline   Reply With Quote
Old 04-24-2014, 11:09 AM   #3
butabika-jon
Newly Registered User
 
Join Date: Apr 2014
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
butabika-jon is on a distinguished road
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

butabika-jon is offline   Reply With Quote
Old 04-24-2014, 10:17 PM   #4
namliam
The Mailman - AWF VIP
 
Join Date: Aug 2003
Location: Amsterdam/The Netherlands
Posts: 11,388
Thanks: 0
Thanked 797 Times in 784 Posts
namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light
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.
__________________
ruoY yppah namliaM si syawla yppah ot pleh
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.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Remember to use the scales (Bottom left) or the thumb (bottom right)

Always looking for new challenges (Amsterdam Area)
namliam is offline   Reply With Quote
Old 05-18-2017, 03:51 PM   #5
theargie
Newly Registered User
 
Join Date: May 2017
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
theargie is on a distinguished road
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.

Last edited by theargie; 05-18-2017 at 04:08 PM.
theargie is offline   Reply With Quote
Old 05-19-2017, 03:50 PM   #6
ridders
but what do I know anyway
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 1,163
Thanks: 23
Thanked 247 Times in 227 Posts
ridders will become famous soon enough
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, 149 views)
File Type: jpg LessonAttendanceReportDesign.jpg (25.4 KB, 143 views)
File Type: jpg LessonAttendanceReportPreview.jpg (25.8 KB, 140 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.
, 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
Reply

Tags
crosstab , crosstab query , row heading

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Crosstab query with dynamic headings miketonny Queries 3 05-19-2011 12:18 AM
Column Headings For Crosstab Query dknj30 Queries 1 05-19-2011 12:09 AM
Crosstab Query Column Headings kevisull Queries 8 12-03-2008 08:08 AM
crosstab query and column headings Stew Queries 7 12-07-2001 07:31 AM
CrossTab Query-Force Row Headings DRananahan Queries 2 11-16-2001 06:21 PM




All times are GMT -8. The time now is 06:35 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


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