SQL help..HAVING MAX(??)

[Jon]

I would like to retrieve the DATA where Date_quote= Max(Price_Data.Date_Quote)

What is wrong this this query?

SELECT Car_Models.Manufacturer, [Family_name] & " " & [Variant] & " " &
[Fuel] AS Name, Car_Models.Family_name, Car_Models.FUEL,
Sum(Price_Data.Price) AS Price,
Max(Price_Data.Date_Quote) AS Max_Date_Quote
FROM Car_Models RIGHT JOIN Price_Data ON Car_Models.Model_ID =
Price_Data.Model_ID
GROUP BY Car_Models.Manufacturer, [Family_name] & " " & [Variant] & " " &
[Fuel], Car_Models.Family_name, Car_Models.FUEL
HAVING Max(Price_Data.Date_Quote);

 

[Douglas J. Steele]

HAVING Max(Price_Data.Date_Quote)

isn't really a useful condition. Unless the maximum value happens to be 30
Dec, 1899, it'll always be non-zero, which means True.

Assuming you have multiple prices in the data base for the same
Manufacturer, Family_name, Variant and Fuel for different values of
Date_Quote and you're trying to get all of the data for the most recent
date, try:

SELECT Car_Models.Manufacturer, [Family_name] & " " & [Variant] & " " &
[Fuel] AS Name, Car_Models.Family_name, Car_Models.FUEL,
Sum(Price_Data.Price) AS Price,
Date_Quote
FROM Car_Models RIGHT JOIN Price_Data ON Car_Models.Model_ID =
Price_Data.Model_ID
WHERE Price_Data.Date_Quote = (SELECT Max(Date_Quote)
FROM Car_Models RIGHT JOIN Price_Data ON Car_Models.Model_ID =
Price_Data.Model_ID)
GROUP BY Car_Models.Manufacturer, [Family_name] & " " & [Variant] & " " &
[Fuel], Car_Models.Family_name, Car_Models.FUEL