Prev: sum weekly data into monthly
Next: 回覆: Select Max, return unwanted rows.
From: e133 on 27 Apr 2010 15:50
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];




From: Tore on 27 Apr 2010 18:31

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
 | 
Pages: 1
Prev: sum weekly data into monthly
Next: 回覆: Select Max, return unwanted rows.