Ordering Crosstab Columns (1 Viewer)

Noodlestall · Mar 5, 2018

I am trying to create a Rolling Month report for the past 3 months.

I have 3 tables
RAGvalues
TeamValues
Dates

These are used to create a table with the data that I require

TeamNumber | RAGMonth | RAGYear | RAG | RollingMonth | DisplayDate
Team1 | 1 | 2018 | R | 13 | Jan-2018
Team2 | 2 | 2018 | G | 14 | Feb-2018
Team3 | 3 | 2018 | G | 15 | Mar-2018

I can get the crosstab table to display with the columns with the DisplayDate however they end up in alphabetical order Feb, Jan, Mar.

I need to order them based on the rolling month ascending, however I don't want the RollingMonth displayed. Is there a way to order the columns by Rolling Month but display the DisplayDate?

plog · Mar 5, 2018

I am trying to create a Rolling Month report for the past 3 months.

Can you demonstrate with data what you are starting with and what you hope to end up with? Provide 2 sets of data:

A. Starting sample data from all relevant tables. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show me the data you hope to end up with in your report when you feed the thing data from A.

Noodlestall · Mar 5, 2018

Hi,

I have attached a document with screenshots of the tables and query outputs.

Thanks

plog · Mar 5, 2018

You provided no table names and I can't tell which is the data you expected the system to produce. Additionally, I don't see any date fields. Do you have any actual date/time fields in your database or are you storing it all as text?

isladogs · Mar 5, 2018

Just create a query sorting by that field but untick so it doesn't show

Code:

SELECT Table1.TeamNumber, Table1.RAGMonth, Table1.RAGYear, Table1.RAG, Table1.DisplayDate
FROM Table1
ORDER BY Table1.RollingMonth;

Replace Table1 with the correct table names & add the join

Why does the title mention 'crosstab' if you are just joining 3 tables?

Noodlestall · Mar 5, 2018

ridders said:
Just create a query sorting by that field but untick so it doesn't show

Code:

SELECT Table1.TeamNumber, Table1.RAGMonth, Table1.RAGYear, Table1.RAG, Table1.DisplayDate FROM Table1 ORDER BY Table1.RollingMonth;

Replace Table1 with the correct table names & add the join

Why does the title mention 'crosstab' if you are just joining 3 tables?

I want to use the crosstab to create a report where the 'DisplayDate' are the column headers.

My understanding is that the column headers in the crosstab auto order alphabetically.

I was hoping that I could somehow join the crosstab to the Dates table, order the columns by the rolling month but not show it. Or change the names of the columns in the query
something like after the pivot
PIVOT table1.RollingMonth In(Select displaydate from Dates where RollingMonth = RollingMonth) and something like that (but I know now written like that)

isladogs · Mar 5, 2018

In your original post you wrote:

These are used to create a table with the data that I require

Perhaps you meant crosstab query??

Noodlestall said:
I want to use the crosstab to create a report where the 'DisplayDate' are the column headers.

My understanding is that the column headers in the crosstab auto order alphabetically.

I was hoping that I could somehow join the crosstab to the Dates table, order the columns by the rolling month but not show it. Or change the names of the columns in the query
something like after the pivot
PIVOT table1.RollingMonth In(Select displaydate from Dates where RollingMonth = RollingMonth) and something like that (but I know now written like that)

Sorry but its as clear as mud
The following is a guess on what you may have:

Try creating a CROSSTAB query without the rolling month field
Then create a SELECT query where you join that to whichever table contains the rolling month. Sort by that but don't show it.

OR
Create a lookup table containing 2 fields RollingMonth (PK) , DisplayMonth.
Then join your crosstab to that??

OR
You could just specify Column Headings in the crosstab property sheet - Jan-2018; Feb-2018; Mar-2018
But that won't work in future months so not a good idea

Noodlestall · Mar 5, 2018

Hopefully I can explain my problem properly after thinking about it a bit more.

I have the following tables:
name: Team - which contains
TeamName - String
Department1 - String
Department2 - String
Month - Integer
Year - Integer

Another table
name: RAGStatusTbl
TeamName - String
RAGStatus - String
Month - Integer
Year - Integer
RollingMonth - Integer

table name: Dates
Month - Integer
Year - Integer
RollingMonth - Integer
DisplayDate - String

Table TEAM joins to RAGStatusTbl via TeamName
RAGStatus joins to Dates using the RollingMonth

I have a query called RAG_Rolling3Months
Select Team.TeamName, Team.Department1, Team.Department2, RAGStatusTbl.RAGStatus, RAGStatusTbl.RollingMonth, Dates.Displaydate

The CrossTab Query I want to end up with the output looking like:

Code:

TeamName | DisplayDate | DisplayDate | DisplayDate
teamname  | RAG            | RAG            | RAG

I have gotten this far but the display dates are in alphabetical order, so currently display:
Feb-2018 | Jan-2018 | Mar-2018

I need these to order to
Jan-2018 | Feb-2018 | Mar-2018

AND be able to update everymonth to date order and not alphabetical.

I hope that makes more sense.

isladogs · Mar 5, 2018

Thank you for the additional detail but it was almost as clear in the first post

Your query RAG_Rolling3Months isn't complete - it only shows the SELECT part.

I have no idea what the crosstab part is actually showing!

Your tables have several duplicate fields which indicates poor table structure

Suggest you upload a cut down version of your db with the 3 tables, the above query & your crosstab.
It will probably be quicker in the long run....

Cronk · Mar 5, 2018

The only way I've been able to get the cross tab query to sort in any other way than either the column or row heading ie adding another non-displayed sorted field has not worked.

My hack is to use the IN (...,...) to sort the columns

Something like

Code:

Transform  first([TabValueField]) select [RowHeadingField] from YourTable group by  [RowHeadingField] pivot [TabValueField] IN ('Jan-2018','Feb-2018',  'Mar-2018')

This forces the output to be in the same order as the IN string.

If the number of columns is unknown, I generate the list in vba, by opening a separate recordset using the same criteria, concatenate the values into a string and create the query in vba.

There is undoubtedly better better query creators than me and if they have an easier solution, I'd like to know about it.

Noodlestall · Mar 6, 2018

Cronk that was exactly what I needed and worked perfectly.

Thanks.

isladogs · Mar 6, 2018

Glad Cronk provided a solution for you

Here's another way of achieving a similar result using a crosstab:

Code:

TRANSFORM First(qryPRecordsALLByMonth.Incidents) AS FirstOfIncidents
SELECT qryPRecordsALLByMonth.PupilID, qryPRecordsALLByMonth.Surname, qryPRecordsALLByMonth.Forename, qryPRecordsALLByMonth.TG, qryPRecordsALLByMonth.StartMonth
FROM qryPRecordsALLByMonth
GROUP BY qryPRecordsALLByMonth.PupilID, qryPRecordsALLByMonth.Surname, qryPRecordsALLByMonth.Forename, qryPRecordsALLByMonth.TG, qryPRecordsALLByMonth.StartMonth
ORDER BY qryPRecordsALLByMonth.Surname, qryPRecordsALLByMonth.Forename, qryPRecordsALLByMonth.YearMonth
PIVOT qryPRecordsALLByMonth.YearMonth;

I've hidden fields containing names in the screenshot below

However the simplest method is often to use method 2 from my previous post:

Try creating a CROSSTAB query without the rolling month field
Then create a SELECT query where you join that to whichever table contains the rolling month. Sort by that but don't show it.

Ordering Crosstab Columns (1 Viewer)

Noodlestall

New member

plog

Banishment Pending

Noodlestall

New member

Attachments

plog

Banishment Pending

isladogs

MVP / VIP

Noodlestall

New member

isladogs

MVP / VIP

Noodlestall

New member

isladogs

MVP / VIP

Cronk

Registered User.

Noodlestall

New member

isladogs

MVP / VIP

Attachments

Similar threads

Users who are viewing this thread