Using Crosstab Query for Report (1 Viewer)

tmyers

Well-known member
Local time
Today, 06:50
Joined
Sep 8, 2020
Messages
1,090
Got it!
Code:
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 & " WHERE (((ContractorCountQry.JobID)=[Forms]![JobQuote]![JobID]))"
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 tblContractors.Contractor" & _
             " FROM tblContractors INNER JOIN tblContractorJob ON tblContractors.ContractorID = tblContractorJob.ContractorID" & _
             " GROUP BY [Contractor]" & _
             " 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

The only problem I currently foresee is when a job is being done and there is no data to populate the crosstab, it still has all the headers from the previous time the code ran (granted all rows are empty). Granted I would "assume" my users wouldn't open the form until there is data they actually want to see.
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:50
Joined
Sep 21, 2011
Messages
14,038
Well obviously I do not know your system, and cannot tell if that join only selects contractors that have jobs?
There is a limit to how many columns you can have on the report. The form you can scroll.

You could always check to see if any records are produced by the strSQL statement? and adjust accordingly?

I wouldn't rely on the users not opening the form. My thoughts would be that they would use it to see how the situation looks at that time.? If empty then great. ? :)

Code:
Debug.Print "recordcount is " & Me.Recordset.RecordCount
 
Last edited:

tmyers

Well-known member
Local time
Today, 06:50
Joined
Sep 8, 2020
Messages
1,090
I just ran a couple test, and if there is no data, the form (which I set as a pop up) comes up blank. I was worried it would show with the previous columns, which while the query itself does, the form does not. So I am perfectly content with that result.

Thank you so much for your help Gas!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:50
Joined
Feb 19, 2002
Messages
42,970
Because I use the intermediate table with the number as the PK, there is never a gap. If you were doing a crosstab for a year and you didn't have data for july, for example, the crosstab would return 11 columns and #7 (july) would be missing.
 

tmyers

Well-known member
Local time
Today, 06:50
Joined
Sep 8, 2020
Messages
1,090
The crosstab adjust itself correctly, but I am having an issue with the report. I made the report with 7 total columns since it would never go over that, but when I use less than that, it throws an error - Run-time error "3070": The Microsoft Access database engine does not recognize " as a valid field name or expression.

So I think that is happening is the on load event renames the unused columns as " or something, which Access doesnt like it.

EDIT:
Never mind. The error happens before the on load event even occurs.
 

tmyers

Well-known member
Local time
Today, 06:50
Joined
Sep 8, 2020
Messages
1,090
I think the problem lies here:
Capture.PNG

The columns that weren't used are changed to that expression. That is interesting.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:50
Joined
Oct 29, 2018
Messages
21,357
The columns that weren't used are changed to that expression. That is interesting.
I could be wrong, but I think that only happens if you open the query in design view when the source data is not available. Otherwise, if you left the query alone, you could still get a prompt, but it won't (shouldn't) be the generic Expr1 type.
 

tmyers

Well-known member
Local time
Today, 06:50
Joined
Sep 8, 2020
Messages
1,090
I think the problem lies in the fact that if the crosstab changes and the column no longer exist, it is putting "" (empty string) as the column header, which Access really doesn't like. How could I add in IF ISNULL to this so rather than an empty string, its just 0? I could then set visibility to false for controls whos caption = 0
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:50
Joined
Sep 21, 2011
Messages
14,038
I think the problem lies in the fact that if the crosstab changes and the column no longer exist, it is putting "" (empty string) as the column header, which Access really doesn't like. How could I add in IF ISNULL to this so rather than an empty string, its just 0? I could then set visibility to false for controls whos caption = 0
You should only be supplying columns names for columns that have data, if you do not want to see empty columns.?
So create your PIVOT statement from known data that populates the columns?
 

tmyers

Well-known member
Local time
Today, 06:50
Joined
Sep 8, 2020
Messages
1,090
What would that PIVOT statement look like? Currently my PIVOT for the crosstab is: PIVOT TEMP_AssignSequence.SeqNum;
After testing small pieces of it, I think the problem lies within the report. Im not currently sure how to fix it but am going to keep tinkering with it.
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:50
Joined
Sep 21, 2011
Messages
14,038
What would that PIVOT statement look like? Currently my PIVOT for the crosstab is: PIVOT TEMP_AssignSequence.SeqNum;
The same as we did for the other crosstab.?
In mine I searched for active users, you searched for contractors.?
 

tmyers

Well-known member
Local time
Today, 06:50
Joined
Sep 8, 2020
Messages
1,090
I fixed the query to not return those empty strings anymore, but now when I try to open the report, is get error (-3007) and it says there is no message for this error.

I assume it is the report not agreeing with the fact some columns present on the report, are no longer present in its record source which is the query.
 

tmyers

Well-known member
Local time
Today, 06:50
Joined
Sep 8, 2020
Messages
1,090
I am unable to open that sadly as I am running 2016 with no access (no pun intended) to earlier versions.
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:50
Joined
Sep 21, 2011
Messages
14,038
I converted it to 2007
 

Attachments

  • CrossTab.accdb
    632 KB · Views: 178

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:50
Joined
Feb 19, 2002
Messages
42,970
I fixed the sample. I did two things.
1. I added column headers to the crosstab. I used 1,2,3,4,5,6,7,8,9,10 because that was all the columns I added. If you have more columns include them here.
2. I added the Nz() around the statement that sets the caption so that if there is no matching entry in the temp table, the caption will be blank.

If you use 3 for both the from and thru value, the record selected has only three expense types and so the crosstab will produce only 3 columns

I'm glad you found that error. Thanks.
 

Attachments

  • BoundDenormalizedForm20201022.zip
    1.5 MB · Views: 200

tmyers

Well-known member
Local time
Today, 06:50
Joined
Sep 8, 2020
Messages
1,090
It works! I think I can finally check this off as complete.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:50
Joined
Feb 19, 2002
Messages
42,970
tmyers, Excellent.

Gasman, Since Access no longer supports A97 I can't even look at the example. I don't know if Roger is maintaining his library or if someone else has taken it over. Do you know?
 

Users who are viewing this thread

Top Bottom