Get latest record

[Sweet16]

I have 2 tables tbl_Rating_D and tbl_Company_Info.

Tbl_Company_Info has the following fields: autoID, name, address, contact person, phone number

Tbl_Rating_D has the following fields: autoID, agencyID (looked up from the name field in the Company Info table), rating, and date.

Companies in the tbl_Company_Info are rated continuously. I would like to create a query that only shows the most recent rating (designated by the date) for each company but am having some difficulty achieving this.

[Stingaway]

Changed a few field names around to avoid reserved names and for expediency, but the general idea would be something like this:

SELECT tbl_company_info.agencyname, Max(tbl_Rating_D.ratingdate) AS MaxOfratingdate
FROM tbl_company_info INNER JOIN tbl_Rating_D ON tbl_company_info.ID = tbl_Rating_D.agencyid
GROUP BY tbl_company_info.agencyname;

Hope it helps.

-stingaway

[June7]

Couple ways to do this. Can use DMax() function. Create a field in query with expression: DMax("[date]","Tbl_Rating_D","agencyID=" & [agencyID]). Then set criteria for this field as [date].

The other is a nested subquery that uses Max() function to return the Max date for each agencyID and then filter for the records where [date] matches the Max date.

Stingaway, that query will return the Max date but will not return the record to show the rating.

[Sweet16]

Thanks for both of the suggestions!!

Do I insert the above expression in the criteria field of the date column?

[June7]

Go ahead and try. If it doesn't work then do what I suggested. I think either should work.

[Sweet16]

June 7 you were correct. The query only returns the agency name and a date. I tried to insert the DMAX critiera into a field but I keep getting an error message stating I am missing and operator.

[June7]

Is agencyID a text field? If so, need apostrophe delimiters. Like:
DMax("[date]","Tbl_Rating_D","agencyID='" & [agencyID] & "'")

Post your exact SQL statement for analysis.

[Sweet16]

Below is the query I inserted. This one gives me all of the records, including the older ones.

SELECT tbl_Rating_D.AgencyID, tbl_Rating_D.Rating, tbl_Rating_D.Date, DMax("[date]","Tbl_Rating_D","AgencyID='" & [AgencyID] & "'") AS Expr1
FROM tbl_Rating_D;

[June7]

Everything returns because you have not given filter criteria for the date:
SELECT tbl_Rating_D.AgencyID, tbl_Rating_D.Rating, tbl_Rating_D.Date FROM tbl_Rating_D WHERE DMax("[date]","Tbl_Rating_D","AgencyID='" & [AgencyID] & "'") = [date];

[Sweet16]

Thanks for the feedback!!! A million thank yous!