Sorting a Crosstab query

[Lkwdmntr]

Hello all, So I decided to start a new thread since my problem is very different then what it was. In the project I'm working on I have one query that gets all the actions I need and then I have a crosstab query that is used in a form and a report.

Query1
SELECT UserID, FullName, WeekNumber, StandardAction, "X" AS Idx, Index
FROM Weekly_Challenges
WHERE Weekly_Challenges.StandardAction<>"Basic Action" AND Weekly_Challenges.StandardAction<>"Basic Action - Medical"
UNION SELECT UserID, Fullname, WeekNumber, StartTimeAction, "X" , Index
FROM Weekly_StartTime_Challenges
WHERE Index < 200;​

Query1_crosstab
PARAMETERS [TempVars]![TmpUserID] Long;
TRANSFORM First(Query1.Idx) AS FirstOfIdx
SELECT Query1.UserID, Query1.StandardAction
FROM Query1
WHERE (((Query1.UserID)=[TempVars]![TmpUserID]))
GROUP BY Query1.UserID, Query1.StandardAction
ORDER BY Query1.WeekNumber
PIVOT Query1.WeekNumber IN (1,2,3,4,5,6,7,8,9,10,11,12);​

I am getting all the actions for all the weeks that I need, but I can't seem to get them sorted properly. This is what my boss is asking for.
-For the first two weeks, sort by index
-Each week after that the action need to added to the bottom of the list as they are chosen per week.
-If an action is swapped and added back on later the "X" will just fit in wherever the action was used first.

I attached an example I made in excel that might help get my point across. I also have a google drive link to my database, it's too big to upload on this site.

If anyone can be of some help it would greatly be appreciated. I need to have this part of my project done by Friday. I never used the crosstab query before, this is all new to me. June7 has been a great help with all of this so far.

https://drive.google.com/file/d/1TKGxsLci5MvfyJx3uIumh1lXGtlwnskh/view?usp=sharing​

If using the database and want to see what it looks like now. When you open the database, Hit "Enter", "History/Reports", Select "Thomas Deiter" and "Next".

 

[theDBguy]

Hi. Are you able to modify the Excel file to include what the crosstab query result looks like now and some instructions on how it becomes the result you want it to look like instead? Thanks.

 

[Lkwdmntr]

Here, I uploaded a screenshot of what my form looks like now. It needs to resemble the excel file. Notice the first two weeks all the actions are X'ed (Row 10 is where all the actions for the first two weeks stop) and then as the plan goes from week to week. One action, or maybe two depending on if they swap an action, will be added at the bottom of the list. I hope that makes it a little more clear.

 

[Lkwdmntr]

I did get all the actions sorted by index now, but I still can't seem to figure out how to go about only using the index sort on the first two weeks and then just as the actions are added the rest of the weeks. Maybe use criteria or VBA. I just can't seem to get it.

PARAMETERS [TempVars]![TmpUserID] Long;
TRANSFORM First(Query1.Idx) AS FirstOfIdx
SELECT Query1.UserID, Query1.StandardAction, Query1.Index
FROM Query1
WHERE (((Query1.UserID)=[TempVars]![TmpUserID]))
GROUP BY Query1.UserID, Query1.StandardAction, Query1.Index
ORDER BY Query1.Index, Query1.WeekNumber
PIVOT Query1.WeekNumber In (1,2,3,4,5,6,7,8,9,10,11,12);
​

 

[June7]

I open db with shift key bypass so I can view and manipulate all objects. Opening db as a user is not helpful for debugging. Therefore I suppose TempVars are not set. When you provide db for debugging, best not to have it 'locked down' - just gets annoying trying to work around the limitations. Full screen and modal really aggravate. I finally figured out how to get TempVars set.

If I understand correctly, you want first 2 weeks of records to sort by Index, regardless of week number then remaining weeks sort by week number then Index within each week? Consider:

PARAMETERS [TempVars]![TmpUserID] Long;
TRANSFORM First(Query1.Idx) AS FirstOfIdx
SELECT Query1.UserID, IIf([WeekNumber]<3,"1-2",[WeekNumber]) AS WN, Query1.Index, Query1.StandardAction
FROM (SELECT UserID, FullName, WeekNumber, StandardAction, "X" AS Idx, Index
FROM Weekly_Challenges
WHERE Weekly_Challenges.StandardAction<>"Basic Action" AND Weekly_Challenges.StandardAction<>"Basic Action - Medical"
UNION SELECT UserID, Fullname, WeekNumber, StartTimeAction, "X" , Index
FROM Weekly_StartTime_Challenges
WHERE Index < 200) AS Query1
WHERE (((Query1.UserID)=[TempVars]![TmpUserID]))
GROUP BY Query1.UserID, IIf([WeekNumber]<3,"1-2",[WeekNumber]), Query1.Index, Query1.StandardAction
PIVOT Query1.WeekNumber In (1,2,3,4,5,6,7,8,9,10,11,12);

 

[Lkwdmntr]

Thanks for the response. This is the direction I was headed as well. The good news is that it is sorting them correctly. Bad news it is grouping them 13 times and giving me 353 records.

I'm surprised you didn't' test this before posting. I am not sure how to fix the database for debugging, but the way I do this is...
----first set the temp vars by Opening the DB, Hit "Enter", "History/Reports", "Action By Plan Week", Select "Thomas Deiter" and "Next".
----then right-click and go into design mode.
----from there your temp vars are set and you can change the SQL and run the crosstab query to see if it works.
----if it does work you can exit and go back into the DB and see what it looks like in the form.

Right now it is grouping by Week 1-2 and then by each week after that.

 

[Lkwdmntr]

Hey Guys, I think I figured it out. What I did is stopped worrying about the crosstab query and how to sort it. Instead, I made a simple query from the crosstab query and just sorted in descending order by each week's column and lastly by index.

SELECT Query1_Crosstab.UserID, Query1_Crosstab.StandardAction, Query1_Crosstab.Index, Query1_Crosstab.[1], Query1_Crosstab.[2], Query1_Crosstab.[3], Query1_Crosstab.[4], Query1_Crosstab.[5], Query1_Crosstab.[6], Query1_Crosstab.[7], Query1_Crosstab.[8], Query1_Crosstab.[9], Query1_Crosstab.[10], Query1_Crosstab.[11], Query1_Crosstab.[12]
FROM Query1_Crosstab
ORDER BY Query1_Crosstab.[1] DESC , Query1_Crosstab.[2] DESC , Query1_Crosstab.[3] DESC , Query1_Crosstab.[4] DESC , Query1_Crosstab.[5] DESC , Query1_Crosstab.[6] DESC , Query1_Crosstab.[7] DESC , Query1_Crosstab.[8] DESC , Query1_Crosstab.[9] DESC , Query1_Crosstab.[10] DESC , Query1_Crosstab.[11] DESC, Query1_Crosstab.Index;​

Thanks for all the help with this. I'm sure this isn't the last time you'll hear from me. I have already been warned that there are more types of reports wanted. Luckily, after doing this one, the others should be a little easier.

I uploaded a screenshot of the final report in case you want to check it out.

 

[theDBguy]

Hi. Congratulations! Glad to hear you got it "sorted" out!


Good luck with your project and Happy New Year!

 

[June7]

I did test and result looked fine. I have only 79 records filtered for UserID 4.

Glad you have a solution anyway.