Crosstab query sorting problem (1 Viewer)

[Wolfroolz]

I currently have a database that generates a dynamic report based on the dates they select as well as the class_id. To generate the report I use a crosstab query. The crosstab query is based on a query with the following fields: CCUST_ID, WEDate, Item_Type, Quantity, DESCRIPTION, Class_Type, Sort_Order

This generates a report with:
Cust ID(Group)
WE Date, Item1, Item2, Item3....etc

The data is in two tables:
Table - Item_Types: ITEM, MULTIPLE, INTERVAL, DESCRIPTION, Class_Type, Sort_Order
Table - Schedule: ID(autonumber), WEDate, Cust_ID, Item_Type, Quantity

I got everything to work except I can't figure out how to sort based on the sort order field. It automatically sorts it alphabetically, and that is not practical. I can't set the order manually because the fields change depending on the user input.

SQL of query below(the above examples were simplified to make easier reading)

Appreciate any help with this. Thanks.

PARAMETERS [Forms]![ReportSelector]![cmbType] Text ( 255 ), [Forms]![ReportSelector]![tStart] DateTime, [Forms]![ReportSelector]![tEnd] DateTime;
TRANSFORM Sum(qCust_Sched.Quantity) AS SumOfQuantity
SELECT qCust_Sched.CUST_ID, qCust_Sched.GROUP_DESC, qCust_Sched.CONTACT, qCust_Sched.PROJECT_TASK, qCust_Sched.LOCATION, qCust_Sched.TEL_NUMBER, qCust_Sched.SECT_CODE, qCust_Sched.WeekEndDate, Sum(qCust_Sched.Quantity) AS [Total Of Quantity]
FROM qCust_Sched
GROUP BY qCust_Sched.CUST_ID, qCust_Sched.GROUP_DESC, qCust_Sched.CONTACT, qCust_Sched.PROJECT_TASK, qCust_Sched.LOCATION, qCust_Sched.TEL_NUMBER, qCust_Sched.SECT_CODE, qCust_Sched.WeekEndDate
PIVOT qCust_Sched.Item_Type;

 

[arnelgp]

create another query based on the xtab query and apply the sorting.

 

[Wolfroolz]

Can you offer some examples? Not really sure what you mean. I am trying to sort the order of how the columns come up in the crosstab query report

Now it reads:
WE Date, Item1, Item2, Item3....etc
I want it to be:
WEDate, Item3, Item1, Item5...etc

Which would be based on the sort order field. I don't know how the query sort would change the column order.

 

[MajP]

Specify column headings
Since the column headings are derived from a field, you only get fields relevant to the data. So, if your criteria limits the query to a period when Nancy Davolio made no sales, her field will not be displayed. If your goal is to make a report from the crosstab, the report will give errors if the field named "Davolio, Nancy" just disappears.

To solve this, enter all the valid column headings into the Column Headings property of the crosstab query. Steps:

In query design view, show the Properties box (View menu.)
Locate the Column Headings property. (If you don't see it, you are looking at the properties of a field instead of the properties of the query.)
Type in all the possible values, separated by commas. Delimit text values with quotes, or date values with #.
For the query above, set the Column Headings property like this (on one line):

"Buchanan, Steven", "Callahan, Laura", "Davolio, Nancy", "Dodsworth, Anne", "Fuller, Andrew", "King, Robert", "Leverling, Janet", "Peacock, Margaret", "Suyama, Michael"
Side effects of using column headings:

Any values you do not list are excluded from the query.
The fields will appear in the order you specify, e.g. "Jan", "Feb", "Mar", ...
Where a report has a complex crosstab query as its Record Source, specifying the column headings can speed up the design of the report enormously. If you do not specify the column headings, Access is unable to determine the fields that will be available to the report without running the entire query. But if you specify the Column Headings, it can read the field names without running the query.

An alternative approach is to alias the fields so the names don't change. Duane Hookom has an example of dynamic monthly crosstab reports.

Multiple sets of values

http://allenbrowne.com/ser-67.html

 

[isladogs]

The quote from Allen Browne in the last post by MajP mentioned dynamic crosstab reports but without an explanation.
These do the opposite of fixed headers by allowing headings to be modified depending on the data.
This approach is very powerful and easy to do once you've seen the method in use.

I've posted several examples of these here.
For example https://www.access-programmers.co.uk/forums/showpost.php?p=1537880&postcount=1

 

[Wolfroolz]

http://allenbrowne.com/ser-67.html

Thanks for this MajP. I think I am a little closer? But I still can't get there. If I just manually enter the headings then I lose the dynamic aspect of this. I want to allow the user to add/change types as needed, and they are not IT people, and they wont have anyone to support this and change the headings. As well, I don't know which class type they will select and there should only be headings for that class type.

However I was able to create a string based on the user selection that has the correct headings needed. I just can't figure out how to use it in the query. I tried putting it in a textbox on the form, and referencing that, but access just uses the reference as a column heading. I tried putting it into a global function and calling the function in sql, but I keep getting an error "Missing ), ], or Item in query expression"

Sql:
PARAMETERS [Forms]![ReportSelector]![cmbType] Text ( 255 ), [Forms]![ReportSelector]![tStart] DateTime, [Forms]![ReportSelector]![tEnd] DateTime;TRANSFORM Sum(qCust_Sched.Quantity) AS SumOfQuantitySELECT qCust_Sched.CUST_ID, qCust_Sched.GROUP_DESC, qCust_Sched.CONTACT, qCust_Sched.PROJECT_TASK, qCust_Sched.LOCATION, qCust_Sched.TEL_NUMBER, qCust_Sched.SECT_CODE, qCust_Sched.WeekEndDate, Sum(qCust_Sched.Quantity) AS [Total Of Quantity]FROM qCust_SchedGROUP BY qCust_Sched.CUST_ID, qCust_Sched.GROUP_DESC, qCust_Sched.CONTACT, qCust_Sched.PROJECT_TASK, qCust_Sched.LOCATION, qCust_Sched.TEL_NUMBER, qCust_Sched.SECT_CODE, qCust_Sched.WeekEndDatePIVOT qCust_Sched.Item_Type In (TypeSortOrder());

Code to create string:
Dim sortstring As String
Dim rs As DAO.Recordset
Dim db As Database
Dim strSQL As String Set db = CurrentDb
strSQL = "SELECT Item FROM TypeSortOrder WHERE Type = '" & Me.cmbType & "'"
Set rs = db.OpenRecordset(strSQL)
intX = 0
If rs.RecordCount > 0 Then Do While Not rs.EOF
sortstring = sortstring & "'" & rs!Item & "'," rs.MoveNext
Loop
End If
Me.sort = Left(sortstring, Len(sortstring) - 1)

Example of string output is: 'LH1S','LH1C','LH112S','LH112C','LH36','LH54'

On the command button that opens the report
StringSO = Me.sort

In seperate module
Global StringSO As String
Public Function TypeSortOrder() As String
TypeSortOrder = StringSO
End Function

 

[MajP]

I think your output should be
[LH1S],[LH1C],...[LH54]
They are not strings but now column names.

 

[MajP]

Examples
https://codingsight.com/understanding-pivot-operator-in-sql/
https://blogs.msdn.microsoft.com/spike/2009/03/03/pivot-tables-in-sql-server-a-simple-sample/

 

[arnelgp]

can u upload a sample db.

 

[Wolfroolz]

Using the examples, I noticed that they added a () as part of the pivot statement. I added that and it no longer gives me that error. Now however when I run the query it is acting like the function is blank, even though when I check the variable it is showing the correct value. I will try to submit a sample db.

 

[Wolfroolz]

Here is a sample with fake data, all entries are for date 7/8/18. If you remove the in statement from the query everything works great, its just all in the wrong order.

BTW I tried it with both '' and [] and didn't seem to make any difference.

Appreciate everyone's help on this. Can't believe how complicated this report is getting. It seemed so simple when I thought it up, lol.

 

[MajP]

I could only make this work in code, but appears to work. Put this in your after update

Private Sub UpdateCrosstab()
  Dim qdf As QueryDef
  Dim db As Database
  Set db = CurrentDb
  Dim strSql As String
  Dim sqlStart As String
  Dim sqlEnd As String
  Dim sqlType
  sqlStart = SQL_Date(Me.tStart)
  sqlEnd = SQL_Date(Me.tEnd)
  sqlType = SQL_Text(Me.cmbType)
  'Update the Select query
  Set qdf = db.QueryDefs("qCust_Sched")
  strSql = "SELECT Cust_ID.CONTACT, Cust_ID.SECT_CODE, Cust_ID.CUST_ID, Cust_ID.GROUP_DESC, Cust_ID.PROJECT_TASK, Cust_ID.LEGACY_ACCT, Cust_ID.TEL_NUMBER, "
  strSql = strSql & "CUST_ID.LOCATION , CUST_ID.ORG, Schedule.WeekEndDate, Schedule.Item_Type, Schedule.Quantity, Items.Description, Types.Type, Items.Sort_Order "
  strSql = strSql & " FROM (Types INNER JOIN Items ON Types.Type = Items.Type) INNER JOIN (Cust_ID INNER JOIN Schedule ON Cust_ID.[CUST_ID] = Schedule.[Cust_ID]) "
  strSql = strSql & "ON Items.ITEM = Schedule.Item_Type WHERE Schedule.WeekEndDate Between " & sqlStart & " And " & sqlEnd & " And Schedule.WeekEndDate Between "
  strSql = strSql & sqlStart & " And " & sqlEnd & " AND Types.Type = " & sqlType
  qdf.SQL = strSql
  Debug.Print strSql
  'Update the XTab
  Set qdf = db.QueryDefs("qCust_Sched_Crosstab")
  strSql = "TRANSFORM Sum(qCust_Sched.Quantity) AS SumOfQuantity SELECT qCust_Sched.CUST_ID, qCust_Sched.GROUP_DESC, qCust_Sched.CONTACT, qCust_Sched.PROJECT_TASK, "
  strSql = strSql & "qCust_Sched.LOCATION, qCust_Sched.TEL_NUMBER, qCust_Sched.SECT_CODE, qCust_Sched.WeekEndDate, Sum(qCust_Sched.Quantity) AS [Total Of Quantity] "
  strSql = strSql & "FROM qCust_Sched GROUP BY qCust_Sched.CUST_ID, qCust_Sched.GROUP_DESC, qCust_Sched.CONTACT, qCust_Sched.PROJECT_TASK, qCust_Sched.LOCATION, "
  strSql = strSql & "qCust_Sched.TEL_NUMBER, qCust_Sched.SECT_CODE, qCust_Sched.WeekEndDate PIVOT qCust_Sched.Item_Type IN (" & TypeSortOrder & ")"
  qdf.SQL = strSql
  
  'I put a subform on the page to see the results
  'Me.subXtab.SourceObject = "Query.qCust_Sched_Crosstab"
End Sub
Public Function SQL_Date(ByVal varDate As Variant) As String
     If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQL_Date = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQL_Date = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    Else
      SQL_Date = "NULL"
    End If
End Function
Public Function SQL_Text(ByVal varItem As Variant) As String
  If Not IsNull(varItem) Then
    varItem = Replace(varItem, "'", "''")
    SQL_Text = "'" & varItem & "'"
  Else
    SQL_Text = "Null"
  End If
End Function

 

[isladogs]

You certainly over complicated this!

I've made a few changes to the query qCust_Sched

a) Fixed the date range criteria - you had added everything twice
b) Formatted the date fields as mm/dd/yyyy in the crirteria to allow the sSQL to work
c) Added the field Sort_Order from the Items table which is already used in the query

Now on the crosstab I've replaced the unnecessary 'IN' statement and replaced it with Sort_Order (Ascending)

The report now works correctly as far as I can tell but do check for yourself
The above solution involves no code changes

 

[MajP]

But then you do not get any column headers on the sort order number as a column header.
For Hardrubber 2 I get

CONN	HOODSM	HOODLG	STIRRUP	FCC
5	8	4	7	8
5	5	87	5	58
9	8	2	8	2

You get

1	2	3	4	5
5	8	4	7	8
5	5	87	5	58
9	8	2	8	2

 

[isladogs]

Ah yes - didn't notice that! How about this instead?

NOTE: I altered the date format to mm/dd/yyyy but the OP can change that back if preferred

Modified crosstab query:

PARAMETERS [Forms]![ReportSelector]![cmbType] Text ( 255 ), [Forms]![ReportSelector]![tStart] DateTime, [Forms]![ReportSelector]![tEnd] DateTime;
TRANSFORM Sum(qCust_Sched.Quantity) AS SumOfQuantity
SELECT qCust_Sched.CUST_ID, qCust_Sched.GROUP_DESC, qCust_Sched.CONTACT, qCust_Sched.PROJECT_TASK, qCust_Sched.LOCATION, qCust_Sched.TEL_NUMBER, qCust_Sched.SECT_CODE, qCust_Sched.WeekEndDate, Sum(qCust_Sched.Quantity) AS [Total Of Quantity]
FROM qCust_Sched
GROUP BY qCust_Sched.CUST_ID, qCust_Sched.GROUP_DESC, qCust_Sched.CONTACT, qCust_Sched.PROJECT_TASK, qCust_Sched.LOCATION, qCust_Sched.TEL_NUMBER, qCust_Sched.SECT_CODE, qCust_Sched.WeekEndDate
[COLOR="Red"][B]PIVOT [Sort_Order] & "_" & [Item_Type][/B][/COLOR];

So this gives headers like 1_CONN, 2_HOODSM etc to ensure sorting is in the desired order
This means we need to modify one line in report GroupHeader0_Format procedure to remove the leading strings 1_, 2_ etc
:

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
   Dim intX As Integer
   Dim i As Integer
    
   i = 1
   '  Put column headings into text boxes in page header.
   For intX = 10 To intColumnCount
      Me("Head" + Format(i)) = [COLOR="red"][B]Mid(rstReport(intX - 1).Name, 3)[/B][/COLOR]
      Me("Head" + Format(i)).GridlineStyleRight = 1
      If intColumnCount < 22 Then
        Me("Head" + Format(i)).Width = 0.7 * 1440
        Else
            If Len(Me("Head" + Format(i))) > 5 Then
             Me("Head" + Format(i)).Width = Len(Me("Head" + Format(i))) * 150
            End If
        Me("Head" + Format(i)).FontSize = 10
        Me.Date.FontSize = 10
      End If
      i = i + 1
   Next intX

   '  Make next available text box Totals heading.
  ' Me("Head" + Format(intColumnCount - 8)) = "Totals"

   '  Hide unused text boxes in page header.
   For intX = (intColumnCount + 2) To conTotalColumns
      Me("Head" + Format(i)).Visible = False
   Next intX
End Sub

Hopefully it is now correct

EDIT: I have used this approach many times in the past. For example a student grades grid where the colour formatting depends not on the grade achieved but the residual grade i.e. the difference between the actual grade achieved & the target grade set earlier in the course. The actual grade is displayed but not the residual which controls the formatting.
The screenshot is low resolution but hopefully the idea is clear

 

[arnelgp]

here is another variation.
i make SortString as module-wise variable,
so we can passed it to the Report (OpenArgs) and
use it in 'PIVOT... IN (..." clause.

i also made a dummy query, 'qryForReportSched' which is
the same query as 'qCust_Sched_Crosstab' (note your
original query i renamed to 'Copy Of qCust_Sched_Crosstab').

on the report Open event, we used this dummy query's SQL.
since they are same as the orig.
then we add the "IN ()" clause to the query.
we replaced the SQL of 'qCust_Sched_Crosstab' with the
modified SQL.

very simple.

 

[Wolfroolz]

here is another variation.

Sorry for the delay in responding, I got pulled off of this to work on something else for a few days.

Wow, everyone here is amazing. I can't thank you all enough for all the help. I appreciate the time and effort that you all took. I wound up going with arnelgp's solution. It was the simplist and most straight forward. I plugged it into my database and it works great.

Since the end user has no idea of the difference in difficulity between asking me to change the font size and asking me to change the headers automatically when they add new types it was extremely important to keep this very dynamic. And this solution solves the problem.

Now just have to build the rest of the database, hopefully it will go more easily.

 

[arnelgp]

good luck with your project!

 

[isladogs]

@Woolfroolz

Pleased that you're happy and have a solution you like.

Both arnel's and my solutions will of course work perfectly for your current situation

I'm interested in why you thought arnel's solution is more flexible.
His involves hard coding the column headers in the PIVOT line of the crosstab query

...PIVOT qCust_Sched.Item_Type IN ('CONN','HOODSM','HOODLG','STIRRUP','FCC');

That of course works in this instance but each time you add a new code ABCD you will need to add that manually.
Similarly if you remove a code, you have to delete it manually
In other words its totally UNDYNAMIC

The beauty of your original code (which I modified only slightly) is the fact that it is DYNAMIC.
It will cope without any code changes if you add, remove or re-sort code types in the future which could save you a lot of time in the future

Good luck with your project also from me

 

[Wolfroolz]

@Woolfroolz

Pleased that you're happy and have a solution you like.

Both arnel's and my solutions will of course work perfectly for your current situation

Arnel's second solution is dynamic. It uses the variable I created to append a line to the original query in vba without having to hard code the original query in vba, which I was trying to avoid. I liked your solution with adding and then taking away the sort order, but I thought his was a little more straightforward. Both would work great. Thanks again for the help, and appreciate the luck.