Seeking out the names which have the highest value.

[Shotodru]

Dear Friends,
I think I may have posted the question once before. Please excuse my
laziness as I cant seem to work around the problem.

I have a query which generates names and their corresponding figures in
three separate fields.

The first field in the query contains the names extracted from a table (say
tbl_customer), the second one field contains amounts extracted from the same
table (tbl_customer), the third field contains figures calculated on the
second field multiplied by a data appearing in another table (say
tbl_bonusrates).

As you may have guessed already, if I change the data in tbl_bonusrates, the
contents of all the records in the third field as outputted by the query
changes correspondingly.

Based on a DMAX operation I can select the highest figure existing in the
query from the third field. However, I also need to extract the names which
have the highest value. It may be just one name or it may be a large number.

For example, if the DMAX value returned from the third field in the query is
1000. There may be ten names which have 1000 standing correspondingly.

What I need to do, is to get those ten names or may be more/less into a text
box (say txt_DisplayHighestNames) on a form (say frm_DisplayDetails).

Can someone please help.

Best regards,
Satadru

 

[Arvin Meyer [MVP]]

There are several ways to do this, but I can think of 1 easy way using a TOP
values query:

SELECT TOP 1 MyTable.ThirdField, MyTable.*
FROM MyTable
ORDER BY MyTable.ThirdField DESC;

This table sorts the values in the calculated field (ThirdField) descending,
so that the larger ones appear on top. Anything that matches the largest
value (TOP 1) will also be displayed because the query has no way of
deciding which of the matching values you want. The only drrawback in doing
this on a calculated field is that there can be very small differences cause
by floating point math that will cause some values to drop off. To combat
this, you may need to use a rounding function to get them all the same.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Shotodru]

Thanks for the input. But can you offer some ideas as to how I can extract
the names and display them through the text box.

Best regards,
Satadru