Oracle to MS access

[JJ1]

Hi all,

I really need to find a maximum of an average, but MAX(AVG(X)) is not supported, so I decided to try a sub query, instead:

> SELECT PCoName, MAX(Average) AS [Max Average]
> FROM Sell
>
> WHERE Average IN (SELECT AVG(Price) AS [Average]
> FROM Sell
> GROUP BY PCoName)
>
> GROUP BY PCoName;

Unfortunately, because my SQL is not the best, the sub query did not work! So, I've written some code which works in Oracle to create a view and then select the maximum:

CREATE VIEW AvgPrices AS
SELECT PCoName,
AVG(Price) AS AvgPrice
FROM Sell
GROUP BY PCoName

SELECT PCoName,
AvgPrice AS MaxAverage
FROM AvgPrices
WHERE AvgPrice = (
SELECT MAX(AvgPrice) FROM AvgPrices

Unfortunately again for me, this doesn't work in Access. Could anyone please tell me
a) what is wrong with my original sub query
or
b) how I can I make the Oracle SQL work in Access.

Any help would be very much appreciated.

Thanks,


James.