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]
SELECT "Dept Total", Sum(POs), Sum(Lates), Sum([No Shows])
FROM [Department Summary]
SELECT "All Dept Total", Sum(POs), Sum(Lates), Sum([No Shows])
FROM [Prepaid Summary]
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];


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 ......
Select 2 as sortfield, ...........
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.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question