Totals query problem

[Gkirkup]

I have a simple query designed to look up a maximum price of a range of items, and give the date of that price.
There are three fields, price, item number and date.
I am doing a MAX on the price, and a GROUP on the item number, so that gives the maximum price of that item, right?
Then I want to know the date of that price. What total would I select? If I use a GROUP on the date, I get multiple items. What I really need is 'no further selection, use the record I already chose with MAX price, and give me the date from that record.
How do I do that?
By the way, this is not for one item, but a query that will provide a max price and date for all items in the table.
Robert

 

[jdraw]

Please show the query you have at the moment.

Since you didn't give table names etc, here is a sample query that may give you some ideas.

[COLOR="Blue"]SELECT CustomerID, CompanyName, VehicleId, Max(ServiceDate) AS MaxOfServiceDate
FROM[/COLOR] [COLOR="Green"][SELECT Customers.CustomerID, Customers.CompanyName, Customers.ContactName
, VInventory.VehicleId, VMaintenance.ServiceDate
FROM Customers 
,VInventory 
,VMaintenance 
where 
Customers.CustomerID = VInventory.CustomerId AND
VInventory.VehicleId = VMaintenance.VehicleID]. AS [%$##@_Alias][/COLOR]
[COLOR="Blue"]GROUP BY CustomerID, CompanyName, VehicleId;[/COLOR]

Colors indicate the parts of the query.

Purpose was to find the CustomerIds, CompanyNames and vehicleId for those Vehicles with the latest service Date.

 

[Gkirkup]

Jdraw: Here is the query:

SELECT Max(ATTHRESHOLD.THRESHOLD) AS MaxOfTHRESHOLD, ATTHRESHOLD.THRESHECI
FROM ATTHRESHOLD
WHERE (((ATTHRESHOLD.THRESHDATE) Between [Enter the start date] And [Enter the end date]))
GROUP BY ATTHRESHOLD.THRESHECI;

This works fine except that because I am using WHERE, I cannot display THRESHDATE (the date) in my query results. If I change the date column to GROUP, I get multiple records, and don't want that.
What I want is MAX of Threshold (the max price) and then the date from that record.

Robert