Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SORTING A PIVOTED UNION Query

Status
Not open for further replies.
sxschech

sxschech

Technical User
Jul 11, 2002
1,034
US
I am trying to sort a union query that is the basis of a cross tab. The query below is the combined query which is a summary query unioned to a presumarized query that is then crosstabbed. For some reason, I have been unable to get the query to sort in the order of Stu_Pop, Term, Status. I even tried breaking it down into several queries (though rather only use one since I have many different queries to do and would not want to have 3 to 6 queries for each) and could not get them to sort properly at their simplest level. I did one query, for 2009/10 and another for 2011 as select queries and then did a union together without the pivot and could not get the sort.

The historical data is already summed (linked in from Excel). The current data is at a detail level so is summed in the query itself (linked in from Oracle).

The first query is for 2011 data and the second query is for 2009 and 2010. When the query runs, the 2009 and 2010 data are together, but the 2011 data appears at the bottom. Here is an example of how it should look:

[tt]
StatusSort TERM STU_POP STATUS TOTAL DOM INTL
10 2009 Freshmen Applied
12 2009 Freshmen Accepted
14 2009 Freshmen Deposit
17 2009 Freshmen Enrolled
10 2010 Freshmen Applied
12 2010 Freshmen Accepted
14 2010 Freshmen Deposit
17 2010 Freshmen Enrolled
10 2011 Freshmen Applied
12 2011 Freshmen Accepted
14 2011 Freshmen Deposit
17 2011 Freshmen Enrolled
10 2009 Transfer Applied
12 2009 Transfer Accepted
14 2009 Transfer Deposit
17 2009 Transfer Enrolled
10 2010 Transfer Applied
12 2010 Transfer Accepted
14 2010 Transfer Deposit
17 2010 Transfer Enrolled
10 2011 Transfer Applied
12 2011 Transfer Accepted
14 2011 Transfer Deposit
17 2011 Transfer Enrolled
[/tt]

This is what is coming out

[tt]
StatusSort TERM STU_POP STATUS TOTAL DOM INTL
10 2009 Freshmen Applied
12 2009 Freshmen Accepted
14 2009 Freshmen Deposit
17 2009 Freshmen Enrolled
10 2010 Freshmen Applied
12 2010 Freshmen Accepted
14 2010 Freshmen Deposit
17 2010 Freshmen Enrolled
10 2009 Transfer Applied
12 2009 Transfer Accepted
14 2009 Transfer Deposit
17 2009 Transfer Enrolled
10 2010 Transfer Applied
12 2010 Transfer Accepted
14 2010 Transfer Deposit
17 2010 Transfer Enrolled
10 2011 Freshmen Applied
12 2011 Freshmen Accepted
14 2011 Freshmen Deposit
17 2011 Freshmen Enrolled
10 2011 Transfer Applied
12 2011 Transfer Accepted
14 2011 Transfer Deposit
17 2011 Transfer Enrolled
[/tt]

Here is the combined query
Code: 
TRANSFORM Sum(TOT) AS SumOfTOT
SELECT StatusSort, TERM, STU_POP, STATUS, Sum(TOT) AS TOTAL
FROM (SELECT Left(qryBJAp.status,2) AS StatusSort, qryBJAp.TERM, qryBJAp.STU_POP, Mid(qryBJAp.STATUS,4) AS STATUS, IIf(qryBJAp.Residency="D","DOM","INTL") AS HeaderRow, Sum(qryBJAp.TOT) AS TOT
FROM qryBJAp
WHERE qryBJAp.STUDENT_LEVEL="UG"
AND TERM = "2011"
GROUP BY Left(qryBJAp.status,2), qryBJAp.TERM, qryBJAp.STU_POP, Mid(qryBJAp.STATUS,4), IIf(qryBJAp.Residency="D","DOM","INTL")
ORDER BY qryBJAp.STU_POP, qryBJAp.TERM 
UNION
SELECT UnpivotData.StatusSort, UnpivotData.Term, UnpivotData.STU_POP, UnpivotData.STATUS, UnpivotData.HeaderRow, UnpivotData.SumTotal
FROM UnpivotData
WHERE UnpivotData.StatusSort In (10,12,14,17) 
AND UnpivotData.HeaderRow<>"TOTAL"
AND UnpivotData.TabName = "UG_DOMInt"
AND TERM Between "2009" and "2010"
)  AS [%$##@_Alias]
GROUP BY StatusSort, TERM, STU_POP, STATUS
ORDER BY STU_POP, TERM
PIVOT HeaderRow;

Here is the broken down version which still won't sort properly:
Query1 Current
Code: 
SELECT Val(Left([qryBJAp].[status],2)) AS StatusSort, qryBJAp.TERM, qryBJAp.STU_POP, Mid(qryBJAp.STATUS,4) AS STATUS, IIf([qryBJAp].[Residency]="D","DOM","INTL") AS HeaderRow, Sum(qryBJAp.TOT) AS TOTAL
FROM qryBJAp
WHERE (((qryBJAp.STUDENT_LEVEL)="UG"))
GROUP BY Val(Left([qryBJAp].[status],2)), qryBJAp.TERM, qryBJAp.STU_POP, Mid(qryBJAp.STATUS,4), IIf([qryBJAp].[Residency]="D","DOM","INTL")
ORDER BY qryBJAp.STU_POP, qryBJAp.TERM;

Query2 Hist
Code: 
SELECT UnpivotData.StatusSort, UnpivotData.Term, UnpivotData.STU_POP, UnpivotData.STATUS, UnpivotData.HeaderRow, Sum(UnpivotData.SumTotal) AS SumOfSumTotal
FROM UnpivotData
WHERE (((UnpivotData.StatusSort) In (10,12,14,17)) AND ((UnpivotData.HeaderRow)<>"TOTAL") AND ((UnpivotData.TabName)="UG_DOMInt") AND ((UnpivotData.TERM) Between "2009" And "2010"))
GROUP BY UnpivotData.StatusSort, UnpivotData.Term, UnpivotData.STU_POP, UnpivotData.STATUS, UnpivotData.HeaderRow
ORDER BY UnpivotData.STU_POP;
Query3 Combined
Code: 
SELECT *
FROM qryUGDomInt_Hist
UNION
SELECT *
FROM qryUGDomInt_Current
ORDER BY STU_POP, TERM, STATUSSORT

I also tried union all and
Code: 
SELECT *
FROM (
SELECT *
FROM qryUGDomInt_Hist
UNION ALL
SELECT *
FROM qryUGDomInt_Current
)
ORDER BY STU_POP, TERM, STATUSSORT

which also failed to sort as I'd like
 
Sort by date Sort by votes

hi,

try this...
Code: 
TRANSFORM Sum(TOT)

SELECT StatusSort, TERM, STU_POP, STATUS

FROM (
SELECT Left(qryBJAp.status,2) AS StatusSort, qryBJAp.TERM, qryBJAp.STU_POP, Mid(qryBJAp.STATUS,4) AS STATUS, IIf(qryBJAp.Residency="D","DOM","INTL") AS HeaderRow, Sum(qryBJAp.TOT) AS TOT

FROM qryBJAp

WHERE qryBJAp.STUDENT_LEVEL="UG"
AND TERM = "2011"

GROUP BY Left(qryBJAp.status,2), qryBJAp.TERM, qryBJAp.STU_POP, Mid(qryBJAp.STATUS,4), IIf(qryBJAp.Residency="D","DOM","INTL")

ORDER BY qryBJAp.STU_POP, qryBJAp.TERM 

UNION

SELECT UnpivotData.StatusSort, UnpivotData.Term, UnpivotData.STU_POP, UnpivotData.STATUS, UnpivotData.HeaderRow
, SUM(UnpivotData.SumTotal)

FROM UnpivotData

WHERE UnpivotData.StatusSort In (10,12,14,17) 
AND UnpivotData.HeaderRow<>"TOTAL"
AND UnpivotData.TabName = "UG_DOMInt"
AND TERM Between "2009" and "2010"
)  AS [%$##@_Alias]

GROUP BY StatusSort, TERM, STU_POP, STATUS

ORDER BY STU_POP, TERM

PIVOT HeaderRow;
[/code

Skip,
[sub]
[glasses]Just traded in my old subtlety...
[b]for a NUANCE![/b][tongue][/sub]
 
Upvote 0 Downvote

oops, missed a Group By
Code: 
TRANSFORM Sum(TOT)

SELECT StatusSort, TERM, STU_POP, STATUS

FROM (
SELECT
  Left(qryBJAp.status,2) AS StatusSort
, qryBJAp.TERM
, qryBJAp.STU_POP
, Mid(qryBJAp.STATUS,4) AS STATUS
, IIf(qryBJAp.Residency="D","DOM","INTL") AS HeaderRow
, Sum(qryBJAp.TOT) AS TOT

FROM qryBJAp

WHERE qryBJAp.STUDENT_LEVEL="UG"
AND TERM = "2011"

GROUP BY
  Left(qryBJAp.status,2)
, qryBJAp.TERM
, qryBJAp.STU_POP
, Mid(qryBJAp.STATUS,4)
, IIf(qryBJAp.Residency="D","DOM","INTL")

ORDER BY qryBJAp.STU_POP, qryBJAp.TERM 

UNION

SELECT
  UnpivotData.StatusSort
, UnpivotData.Term
, UnpivotData.STU_POP
, UnpivotData.STATUS
, UnpivotData.HeaderRow
, SUM(UnpivotData.SumTotal)

FROM UnpivotData

WHERE UnpivotData.StatusSort In (10,12,14,17) 
AND UnpivotData.HeaderRow<>"TOTAL"
AND UnpivotData.TabName = "UG_DOMInt"
AND TERM Between "2009" and "2010"

Group By
  UnpivotData.StatusSort
, UnpivotData.Term
, UnpivotData.STU_POP
, UnpivotData.STATUS
, UnpivotData.HeaderRow
)  AS [%$##@_Alias]

GROUP BY StatusSort, TERM, STU_POP, STATUS

ORDER BY STU_POP, TERM

PIVOT HeaderRow;

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Hi Skip,

Thanks for the quick response. Sadly, the query produces the same sort result as the original.
 
Upvote 0 Downvote


How about posting some data that this query can use. Just the FIRST of the two Unions, please.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Here is a list for the qryBJAp
[tt]
TERM STU_POP STATUS RESIDENCY TOT
2011 Transfer 10.Applied D 1
2011 Transfer 12.Accepted D 1
2011 Transfer 14.Deposit D 1
2011 Transfer 17.Enrolled D 0
2011 Transfer 10.Applied D 1
2011 Transfer 12.Accepted D 1
2011 Transfer 14.Deposit D 1
2011 Transfer 17.Enrolled D 1
2011 Transfer 10.Applied D 1
2011 Transfer 10.Applied I 1
2011 Transfer 12.Accepted I 0
2011 Transfer 14.Deposit I 0
2011 Transfer 17.Enrolled I 0
2011 Freshmen 10.Applied I 1
2011 Freshmen 12.Accepted I 1
2011 Freshmen 14.Deposit I 0
2011 Freshmen 17.Enrolled I 0
2011 Freshmen 10.Applied D 1
2011 Freshmen 12.Accepted D 0
2011 Freshmen 14.Deposit D 0
2011 Freshmen 17.Enrolled D 0
[/tt]

Here is a list for Unpivot
[tt]
StatusSort Term STU_POP STATUS HeaderRow SumTotal
10 2009 Freshmen Applied DOM 1843
10 2009 Freshmen Applied INTL 219
12 2009 Freshmen Accepted DOM 1353
12 2009 Freshmen Accepted INTL 119
14 2009 Freshmen Deposit DOM 269
14 2009 Freshmen Deposit INTL 23
17 2009 Freshmen Enrolled DOM 209
17 2009 Freshmen Enrolled INTL 7
10 2010 Freshmen Applied DOM 2041
10 2010 Freshmen Applied INTL 249
12 2010 Freshmen Accepted DOM 1411
12 2010 Freshmen Accepted INTL 139
14 2010 Freshmen Deposit DOM 280
14 2010 Freshmen Deposit INTL 37
17 2010 Freshmen Enrolled DOM 230
17 2010 Freshmen Enrolled INTL 15
10 2009 Transfer Applied DOM 337
10 2009 Transfer Applied INTL 50
12 2009 Transfer Accepted DOM 183
12 2009 Transfer Accepted INTL 26
14 2009 Transfer Deposit DOM 78
14 2009 Transfer Deposit INTL 11
17 2009 Transfer Enrolled DOM 47
17 2009 Transfer Enrolled INTL 3
10 2010 Transfer Applied DOM 443
10 2010 Transfer Applied INTL 261
12 2010 Transfer Accepted DOM 881
12 2010 Transfer Accepted INTL 120
14 2010 Transfer Deposit DOM 457
14 2010 Transfer Deposit INTL 53
17 2010 Transfer Enrolled DOM 307
17 2010 Transfer Enrolled INTL 26
[/tt]
 
Upvote 0 Downvote
I think I may have found out the problem. Since I couldn't get it to sort in Access, I tried copying and pasting the data results to excel to see if it would sort there and it didn't. Upon closer inspection, I noticed that some of the STU_POPs had a leading space, once I got rid of the space, the sort seems to have sorted itself out. I modified the query thusly:

TRIM(UnpivotData.STU_POP) AS STU_POP

I'll see how it goes with the rest of my queries.

Thanks for your assistance and sorry to have bothered you.
 
Upvote 0 Downvote


Code: 
WHERE [b][red]STUDENT_LEVEL[/red][/b]=‘UG’
Where is [red]STUDENT_LEVEL[/red] in your sample data???

Please do not waste our time with incomplete information!


Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Skip,

Perhaps you hadn't read my previous post where I identified and solved the problem and did apologize for "wasting your time"

Thanks for your assistance and sorry to have bothered you.
Click to expand...

Sorry that I didn't provide complete data, the data that I provided already filtered out the "UG", so my mistake for leaving that out of the query sample data result set.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

C
  • Question
Query expression negative to a 0
Replies
6
Views
365
dhookom
dhookom
serino
  • Locked
  • Question
Help with a Query. 1
Replies
13
Views
870
strongm
strongm
TheresAlwaysAWay
  • Locked
  • Question
Union query provides different reslt than when summing the combined queries individually 1
Replies
12
Views
1K
TheresAlwaysAWay
TheresAlwaysAWay
C
  • Question
Run-time error '3075': Syntax error (missing operator) in query expression 'Birthday = and Birthmonth ='.
Replies
3
Views
249
OzFoxy
OzFoxy
Swi
  • Locked
  • Question
Select X of each variation of a field 1
Replies
8
Views
433
rashmibala
R

Part and Inventory Search

Sponsor

Back
Top