Showing Top, Lowest and Average in Access 1

[sap1958]

Sample Data
ID Year Amt
01 2002 1000
01 2002 50
01 2003 500
02 2002 750
02 2002 25
02 2002 30

In this scenario I want to show the highest amt for ID 01 or 1000 and the lowest amt or 50 for the year 2002. For 2003 I show 500 for ID 01. For ID 02 I show 750 for the highest, 25 for the lowest.

Then I want to total up all records, sort by year and ID and do an average.
Sample Output for ID 01
ID High Low Avg
01 1000 50 50

Any ideas!

 

[PHV]

You wanted this ?

SELECT [Year],ID,Max(Amt) AS High,Min(Amt) AS Low,Avg(Amt) AS [Avg]
FROM yourTable
GROUP BY [Year],ID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sap1958]

Thanks for the information. One more queestion, when I got the file the Amt came over as a text not currency. Is there a way in an access query to covert the text to a currency

 

[PHV]

Use the CCur function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886