If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Union query not displaying select statement
I have a union query which provides column totals for a detail query.
Everything works as expected but for some reason when I run the query the results will not always display the last select statement - "Percent of total" row. The odd occurence is if I run the query with a filter that limits the records to one viewable page in datasheet view the "percent of total" row is displayed. If the query output is more than one viewable page the "percent of total" row does not display. Also, if I remove "all" from the last union statement the "percent of total" row will display with all filters but the ordering in not correct. I need to have the three union selects as the last three rows - without the "all" they are ordered with the name column. Below is the sql. Any help on getting the "percent of total" to always display or the ordering corrected is appreciated. PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime; SELECT [Create].[Vendor Name], Count([Create].[Nbr]) AS POs, Sum(Abs([LOS]='Late')) AS Lates, Sum(Abs([LOS]='NoShow')) AS [No Shows] FROM [Create] WHERE ((([Create].[Terms])="P") AND (([Create].[Date]) Between [Enter Start Date] And [Enter End Date]) AND (([Create].Dept)=[Enter Dept])) GROUP BY [Create].[Vendor Name] UNION ALL SELECT "Dept Total", Sum(POs), Sum(Lates), Sum([No Shows]) FROM [Department Summary] UNION ALL SELECT "All Dept Total", Sum(POs), Sum(Lates), Sum([No Shows]) FROM [Prepaid Summary] UNION ALL SELECT "Percent of Total", Round(((Sum(POs)/(Select Sum(POs) From [Prepaid Summary]))*100),2), Round(((Sum(Lates)/(Select Sum(Lates) From [Prepaid Summary]))*100),2), Round(((Sum([No Shows])/(Select Sum([No Shows]) From [Prepaid Summary]))*100),2) FROM [Department Summary]; |
#2
|
|||
|
|||
Union query not displaying select statement
You should add "order by" statement after the last union select, at the bottom of your statement. When I need some special ordering in a union I often add a sort field: Select 1 as sortfield, field2, field3 ...... union Select 2 as sortfield, ........... union Select 3 as sortfield, ........ order by Sortfield Disappearance of last record(s) I cannot explain. May be in some cases it does not return any record at all? By using a sortfield you can force it to appear at the top and check out if it has something to do with its position in the list. Regards |
Thread Tools | |
Display Modes | |
|
|