Using Crosstab Query for Report

[tmyers]

I have been reading various articles on how to accomplish this. Such as:

Create Dynamic Report using VBA - Post.Byes

This article contains three different approaches to creating dynamic reports. Dynamic report from user defined SQL SELECT statement (Author: mmccarthy (http://www.thescripts.com/forum/member.php?u=29327)) Dynamic report designed to be used with Crosstab Queries (Author: Nico5038...

bytes.com

and

Creating Microsoft Access Monthly Summary Crosstab Query Reports without Programming Code

Creating Microsoft Access Summary Crosstab Query Reports using a Crosstab Query and No Code showing Monthly Totals

www.fmsinc.com

However, I had a hard time understanding those. I will try and break this down as best I can.

My crosstab takes various counts for items from various contractors for a given project. So for item A, contractor1 has 5, contractor2 has 8, contractor3 has 2 etc. The crosstab then very nicely shows those like so:

. Ignore the total column, that is going to be removed as it isn't relevant. From project to project, the contractors can change, but there are typically never more 4. How could I get a report to work off this crosstab query? The column headers would be the contractor name, so I couldn't manually set the column headers as my db has upwards of 70 contractors.

Based on my research, this is going to require VBA to accomplish. I would like the report to look almost like a monthly report. Where the contractors name are the months if that makes sense.

I know more info/detail will likely be needed, so ask away.

 

[Pat Hartman]

Here is a sample. It uses a temp table to hold the column headings that the crosstab will produce and assign them to a sequence number. Look the Report from variable crosstab to see it in action. For purposes of the example, the process is broken into two steps. In production, you would do them both with one click. Here are some pictures also.

 

[Gasman]

The way I have approached this in the past is like below, though this is for a form.
This way the columns headings are dynamic by what is in strCaller variable.

HTH

Public Sub Form_Open(Cancel As Integer)
Dim strCaller As String, strSql As String, strQuery As String

strCaller = PersonList()

strQuery = "qryWeekCallSummary_Crosstab"

'strSQL = "PARAMETERS pCallSystem Text ( 255 );"
strSql = strSql & " TRANSFORM Sum([SumOfCallSecs]/86400) AS Duration"
strSql = strSql & " SELECT qryWeekCallSummary.WeekEndDate"
strSql = strSql & " FROM qryWeekCallSummary "
If Len(gCallSystem) > 0 Then
    strSql = strSql & " WHERE qryWeekCallSummary.CallSystem = '" & gCallSystem & "'"
End If
strSql = strSql & " GROUP BY qryWeekCallSummary.WeekEndDate"
strSql = strSql & " ORDER BY qryWeekCallSummary.WeekEndDate DESC"
strSql = strSql & " PIVOT qryWeekCallSummary.Caller IN (" & strCaller & ")"

'CurrentDb.QueryDefs(strQuery).Parameters("[pCallSystem]") = gCallSystem
CurrentDb.QueryDefs(strQuery).SQL = strSql
Me.RecordSource = strSql
End Sub

Public Function PersonList() As String
    Dim lngCount  As Long
    Dim strSql    As String
    Dim strPeople As String
    giMaxPeople = 10
    
    
    strSql = " SELECT [CallerName]" & _
             " FROM tblCaller" & _
             " WHERE [Required] = True" & _
             " ORDER BY [CallerName]"
             
    With CurrentDb.OpenRecordset(strSql)
        Do Until .EOF
            lngCount = lngCount + 1
            If lngCount > giMaxPeople Then Exit Do
        
            strPeople = strPeople & Chr(34) & !CallerName & Chr(34) & ","
            .MoveNext
        Loop
    End With
    
    If Right(strPeople, 1) = "," Then
        PersonList = Left(strPeople, Len(strPeople) - 1)
    End If
    
End Function

 

[tmyers]

I am looking at your example db now Pat and trying to understand what all is happening. I will let you know if I cant figure something out.

 

[tmyers]

@Pat Hartman I am having trouble finding your sequence of events. What you have going on here is pretty complex (from my eyes).

It appears with the first button, you clear the temp table, but you almost instantly lose me the moment I try to make heads or tails of your ResetSeed module. My best guess is the two are resetting the autonumber fields? That is purely based off your in code comments. From there you go back into your SQL string (which in of itself I don't fully understand what it is doing).

This whole thing I think is beyond my current ability to understand (which is probably why I had trouble reading the articles I posted). I may have to pass on this one for now.

 

[Pat Hartman]

I know it is confusing. It is very high level stuff.

The temp table uses an autonumber as the ID. I could have used a generated sequence number and you might have been able to follow that logic. What the Seed does is it tells Access to start generating autonumbers at 1 again. If I didn't do that, the numbering for the autonumber would continue with the highest number that was previously in the table (possibly 20). So the first row in the clean table would be ID = 21 and that wouldn't map to the field names in the report. This table always needs to start renumbering from 1.

Put a stop in the code. Press the first button and step through the code until you understand what it is doing. While the code is paused, you can look at the values in tables to see what is going on.

Personally, I never use the tabbed view because I seem to always want to look at multiple objects at the same time and it is too annoying to hae to keep swapping from tab to tab. So, if you have the same problem, switch to Overlapping Windows view and then close and reopen the database.

 

[tmyers]

Resetting the autonumber makes sense. I can sort of follow that as I am stepping through it. I am currently trying to wrap my head around the rest of it and adapting it for my use.

I will mess with this for a bit and see if I can grasp it and get it working, or run out the door screaming in failure.

 

[tmyers]

Yeah, I am going to have to come up with another method other than a crosstab. This is far too advanced for me. I read something that suggested using a report for row headings as a parent report, then make the column headings a subreport then the values another subreport.

 

[Pat Hartman]

There is a different solution at www.fmsinc.com as a download. I didn't like it which is why I created my solution.

The report is fixed in format. I think mine has about 12 fixed columns. That is the actual limitation. The number of columns in the report needs to be fixed. It could be 3, it could be 100 (although you'd never be able to read the report if you had that many). Therefor the control names for the columns are fixed. I elected to use numbers 1,2,3,4,.... Let's say we're going to make a report of 12 rolling months. So, the crosstab starts at the first month of the period which could be any month. The base query selects the "income" amount using a query like:
Select CompanyName, Sum(IncomeAmt) as SumIncome, Format(IncomeDate, "yyyy/mm") as IncomeMonth
From YourTable
Group By CompanyName, Format(IncomeDate, "yyyy/mm")
Order By CompanyName, Format(IncomeDate, "yyyy/mm")

The trick is to convert 201911 to 1, 201912 to 2, 202001 to 3, etc so that the columns for the report start with Nov 2019 and go through Oct 2020. And that is done by appending the "header" rows to the temp table and then joining to it.

 

[Gasman]

Yeah, I am going to have to come up with another method other than a crosstab. This is far too advanced for me. I read something that suggested using a report for row headings as a parent report, then make the column headings a subreport then the values another subreport.

My method might not be as slick, but it is a lot simpler?

 

[tmyers]

Yours was a good bit easier to understand Gas. I think I am going to try and shy away from the crosstab however. It is proving to be a huge pain for someone of my level to contend with.

 

[Gasman]

Yours was a good bit easier to understand Gas. I think I am going to try and shy away from the crosstab however. It is proving to be a huge pain for someone of my level to contend with.

Not really. As I said in your other thread, I have not used crosstabs that much. I used one when I created a DB when working at a bank, and that had hardcoded column headers. After I left they called me on how to change a column heading for a team leader.

Then I progressed to dynamic column headers by that method shown.
If I had to do one again, then I would review the one I have already created, so once you get one under your belt, you can always refer to that one.?

Up to you, but if the report requires it, then you need to get stuck in.

That one shown was for me to show total time spent on the phone from a call recording/logging system and only made sense in that format.

 

[tmyers]

Gas, I am trying your method now. Would I make the crosstab a subform and put at code in it?

 

[tmyers]

I am having a rough time changing your SQL statements to what I need them to be. My knowledge of SQL is a little lacking to be able to understand what your code is doing to be able to change it to my needs.

 

[Gasman]

I never (well hardly ever) write SQL from scratch, especially for joins etc.
I use the query QBE window?

So these are the steps I take.
Create the query with the data you need.
Then change it to a crosstab.

Then take the sql generated and amend that last PIVOT line to reflect the dynamic column headings. For now though, just get the crosstab working with fixed headings. That is how that code I posted was created.

Small steps, small steps.

 

[tmyers]

I see. That makes much more sense. Let me take another stab at it and I will get back to you.

 

[tmyers]

After getting a slew of errors that I think I managed to fix correctly, I am getting "Cannot specify fixed column heading "1" in a crosstab query more than once" error. The modified code is:

Option Compare Database

Public Sub Form_Open(Cancel As Integer)
Dim strContractor As String, strSql As String, strQuery As String

strContractor = Contractorlist()

strQuery = "ContractorCountQry_Crosstab"

strSql = "PARAMETERS [Forms]![JobQuote]![JobID] Short;"
strSql = strSql & " TRANSFORM First(ContractorCountQry.Count) AS FirstOfCount"
strSql = strSql & " SELECT ContractorCountQry.TypeName"
strSql = strSql & " FROM ContractorCountQry "
strSql = strSql & " GROUP BY ContractorCountQry.TypeName"
strSql = strSql & " PIVOT ContractorCountQry.Contractor IN (" & strContractor & ")"


CurrentDb.QueryDefs(strQuery).SQL = strSql
Me.RecordSource = strSql
End Sub

Public Function Contractorlist() As String
    Dim lngCount  As Long
    Dim strSql    As String
    Dim strContractor As String
    giMaxContractor = 8
    
    
    strSql = " SELECT [Contractor]" & _
             " FROM tblContractors" & _
             " ORDER BY [Contractor]"
            
    With CurrentDb.OpenRecordset(strSql)
        Do Until .EOF
            lngCount = lngCount + 1
            If lngCount > giMaxContractor Then Exit Do
        
            strContractor = strContractor & Chr(34) & !Contractor & Chr(34) & ","
            .MoveNext
        Loop
    End With
    
    If Right(strContractor, 1) = "," Then
        Contractorlist = Left(strContractor, Len(strContractor) - 1)
    End If
    
End Function

 

[tmyers]

I think I figured it out. But I will leave the above edited post to show the changed code.

 

[Gasman]

Firstly my PARAMETERS statement was commented out for some reason.
You want the contractor names, not their ID as that will be the column headings?

Plus I was taking my users from a user table, where they only occur once?. You appear to be taking from a ContractorJobs table where they likely exist more than once, so will need to group by ContractorID to get only one of each.

Try and take some time to understand how this is working.

 

[tmyers]

Yeah I am still tinkering. I tend to post as the thought rolls through, which gets me in trouble from time to time.