Total query order

  • Thread starter Thread starter Jim Hicks
  • Start date Start date
J

Jim Hicks

I am running a total query to summarize some information
from different locations of accounts. All accounts have
a 0 location and many have other locations as well.

I run a nomal query to sort the account number and
location fields ascending. I then run a total query
grouping on account number and selecting the "first"
option for the location. I have a couple of accounts
that display location numbers that are not 0. The vast
majority of accounts display normally. Looking at the
previous sort query that the total query is based on, the
0 locations of these accounts are included. Both fields
are numeric data type decimal formatted.

What can the problem be?
 
The result returned by "First" is effectively arbitrary.

Try using "Min" instead.
 
Use TOP 100 PERCENT keywords in your subquery, otherwise
your order by will be ignored.

--qryTotals
SELECT First([MyField]) FROM
qryAlreadySorted


--qryAlreadySorted
SELECT TOP 100 PERCENT UniqueID, MyField
FROM MyTable
ORDER BY MyField


David Atkins, MCP
 
Back
Top