Sorting a Union Query by Sum, Where to put Desc?

[dohernan via AccessMonster.com]

I have a Query that looks at 2 tables then fills out 2 fields, Form Type &
Quantity.
I want it to list them by the highest Quantity on down from both tables, and
am not sure where to stick the DESC.
Thanks.


SELECT PersonnelForms2009.[Form Type],
Count(PersonnelForms2009.[Form Type]) AS QTY
FROM PersonnelForms2009
WHERE [Completed] Between Forms!PersonnelFormQueries!StartQueryDate
And
Forms!PersonnelFormQueries!EndQueryDate
GROUP BY PersonnelForms2009.[Form Type]
UNION ALL SELECT tblOtherForms.[Form Type], Sum(Qty)
FROM tblOtherForms
WHERE [Completed] Between Forms!PersonnelFormQueries!StartQueryDate
And
Forms!PersonnelFormQueries!EndQueryDate
GROUP BY tblOtherForms.[Form Type];

 

[Bob Barrows]

I have a Query that looks at 2 tables then fills out 2 fields, Form
Type & Quantity.
I want it to list them by the highest Quantity on down from both
tables, and am not sure where to stick the DESC.
Thanks.


SELECT PersonnelForms2009.[Form Type],
Count(PersonnelForms2009.[Form Type]) AS QTY
FROM PersonnelForms2009
WHERE [Completed] Between Forms!PersonnelFormQueries!StartQueryDate
And
Forms!PersonnelFormQueries!EndQueryDate
GROUP BY PersonnelForms2009.[Form Type]
UNION ALL SELECT tblOtherForms.[Form Type], Sum(Qty)
FROM tblOtherForms
WHERE [Completed] Between Forms!PersonnelFormQueries!StartQueryDate
And
Forms!PersonnelFormQueries!EndQueryDate
GROUP BY tblOtherForms.[Form Type];

A union query can have only one "order by" clause that appears at the
end of the query:
select ...
union
select ...
order by fieldname desc

 

[dohernan via AccessMonster.com]

Thank you, I updated to the SQL below, quit and Re-started Access, and the
Order By is being ignored.

The report is currently sorting by Form names, Alphabetically Descending,
instead of by the Qty.

I've been looking at the Query and at the Report Design itself, trying to see
where I may have set it to that accidentally...

++++++++
SELECT PersonnelForms2009.[Form Type],
Count(PersonnelForms2009.[Form Type]) AS QTY
FROM PersonnelForms2009
WHERE [Completed] Between Forms!PersonnelFormQueries!StartQueryDate
And Forms!PersonnelFormQueries!EndQueryDate
GROUP BY PersonnelForms2009.[Form Type]
UNION ALL
SELECT tblOtherForms.[Form Type], Sum(Qty)
FROM tblOtherForms
WHERE [Completed] Between Forms!PersonnelFormQueries!StartQueryDate
And Forms!PersonnelFormQueries!EndQueryDate
GROUP BY tblOtherForms.[Form Type]
ORDER BY [QTY] DESC;

 

[dohernan via AccessMonster.com]

I got it sorting in both the Query & Report, thanks.

 

[dohernan via AccessMonster.com]

I got it sorting in both the Query & Report, thanks.

 

[John W. Vinson]

Thank you, I updated to the SQL below, quit and Re-started Access, and the
Order By is being ignored.

The report is currently sorting by Form names, Alphabetically Descending,
instead of by the Qty.

A Report's sort order is controlled by the Report's Sorting and Grouping
property. It ignores the Query's sort order.

 

[David W. Fenton]

I got it sorting in both the Query & Report, thanks.

Just for the record, in UNION queries, you can define your ORDER BY
using 0-based ordinal numbers for the fields, in your case it would
be ORDER BY 1 DESC.

 

[John Spencer]

David,
I may be wrong, but I think the ordinal numbers for the order by clause are
one-based and not zero-based.

So to sort by the second column, you would use
ORDER BY 2 Desc

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[David W. Fenton]

I may be wrong, but I think the ordinal numbers for the order by
clause are one-based and not zero-based.

So to sort by the second column, you would use
ORDER BY 2 Desc

OK, you're right -- I excoriate people for posting without testing,
and here I am, hoist by my own petard.

I'm constantly getting confused about which things are zero-based
indexes and which are not.

 

[Marshall Barton]

excoriate

Nice word, David. Deinitely more polite than "ripping a new
one" ;-)

Never know what kind of new things can be learned in these
NGs.