MS Access help this may be simple to do but bear with me

[codingman]

If I had a table in a database with items and their prices and I wanted to do a query to get not only the highest price using the MAX function but also the item with that highest price what would I do?



I tried this already but for some reason could not get it to work, is it possible for someone to give an example please? Or say how it would be done. I know you use the max function for the highest price but when it came to getting the item or itemname itself it was just a problem.

Thank you.

[orange]

Would be easier to assist if you provided the SQL of the query you have tried.

[codingman]

Thank you so much for replying and so quickly too.

This is the SQL
but the thing is in this I just got the highest price but I also wanted the name of that item with the highest price from the Inventory Table


SELECT DISTINCTROW Max(Inventory.Price) AS MaxOfPrice
FROM INVENTORY;

Not sure if this helps but thank you.

[JoeM]

Assuming the name of the Item field is "Item", try something like this:

SELECT Item, Price
FROM Inventory
WHERE Price = 
(SELECT Max(Inventory.Price) AS MaxOfPrice
FROM Inventory);

[codingman]

Again thank you I was trying to test it but for some reason my MS Access isn't working.

But I'm really glad you came up with this solution.

[JoeM]

What is the name of your Item field?
Did you coipy/paste this code directly into the "SQL View" of a Query?

[CJ_London]

since this is a subquery you need to alias one of the references to inventory

SELECT Item, Price
FROM Inventory
WHERE Price = 
(SELECT Max(Price) AS MaxOfPrice
FROM Inventory as T)

Alternatively treat as a virtual table which will be more efficient for larger datasets

SELECT A.Item, A.Price
FROM Inventory A
    INNER JOIN 
        (SELECT Max(Price) AS MaxOfPrice FROM Inventory) B
           ON A.Price=B.MaxOfPrice

[codingman]

I wanted to but I can't get my ms access working at the moment so I have to wait before I can test it.

[codingman]

Thank you For your reply I will try this solution as well.

[JoeM]

I wanted to but I can't get my ms access working at the moment so I have to wait before I can test it.

OK. I misunderstood.
I thought you meant that my solution wasn't working for you, but see you mean that you are actually having a problem getting MS Access to work.