Currency sorting

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

Hi

I would like the query to return the top ten highest currency value, and the
rest as others if possible. How do we sort currency in queries.

Thanks is advance

Richard

--
 
Richard

Currency is just another number type.

I don't understand "and the rest as others if possible".

Use the TOP expression (check query properties) to get the Top 10. Use the
Sort row in the query design window to sort by a field.

Good luck

Jeff Boyce
<Access MVP>
 
Hi Jeff

Thanks for taking time to reply.

"and the rest as others if possible". means that if I have 20 records, it
will show ten records and the other 10 records sum up as 'others' i.e
showing the list box as having 11 records.

for eg

Harry $45,000
Lucy $20,000
.....
....
Others $15,000

Thanks again

Richard
 
Richard

OK, now I get what you're trying to do.

One approach would be to treat the top 10 and the "others" as separate
operations. You can use the TOP 10 suggestion from my previous reply to get
the first part.

The second is a bit trickier. First, I'd take the query that gives the TOP
10 and do an "unmatched" query against the original table to find those that
are "other". Then I'd run a Totals query against that (you'll have to
experiment, you might be able to convert the unmatched query to a Totals
query, or you might have to use the unmatched query as the source for a new,
third, Totals query). In the Totals query, create an expression for one
field, something like:

Other: "Others"

and select the Amount field, setting the "Totals" row to Sum.

Finally, if you need to have all these showing in a single query's output
(by the way, how are you planning to SORT these?!), you'll have to build a
UNION query (check Access HELP) to bring together both the TOP 10 and the
Others amounts.

Good luck!

Jeff Boyce
<Access MVP>
 
Hi Jeff

Thanks a lot for the tip. Seems very complicated.. but will try it out

Thanks again

Richard
 
Back
Top