Need Help to Report the Info on a Query

[elephant97]

I currently have a query that sucessfully produces the information I need. It has 8 columns of data. What I am currently doing is taking an average of each column and placing that number on a form. What I need is some VBA or SQL to allow me to report the values on the row (for all 8 columns) of the MAX of column 1. I also need to do the same for the 2nd Highest value of column 1. Any help is appreciated. Here is some sample data:


Date--------Cash-----FTMR-----%Pay
7/1/2004---86.47----38.59----86.10%
7/8/2004---82.47----32.18----83.70%
7/15/2004--81.00----44.59----85.80%
7/22/2004--89.22----42.59----86.00%
7/29/2004--88.43----41.66----83.70%

So for this example, I would want to be able to place the data from 7/22/04 and 7/29/04 on my form. Thanks!

 

[bat17]

In the Query design view, select properties and under "Top Values" enter 2. You will need to sort descending on Column 1. This will then just return the two values you need. If you want to show both these records at the same time then create a continuous subform based on the query

HTH

Peter

 

[elephant97]

Isn't there a way to just put an expression in the control source for the text box I want to display the value(s) in? I thought I could "filter" the return of the query in the control source....