Trying to pull one record with the most recent date

[Guest]

I have a database with two tables. The first table is called “Company
Infoâ€, this contains the name, address, phone numbers and contact names. The
second table is called “Notesâ€, this is for each entry or to record each
contact I have with the customer. I have a form created that ties each note
to the proper Company as it is filled out. With each “Note†record there is
a field called “Follow up Dateâ€. If I call the customer, I fill out the
“Note†documenting the conversation, and fill in a date to follow up. When
the follow up date arrives, I add another note with a new Follow up date.

I have a query and report to pull all customers and under each Customer are
the attached “Note†records based on the Date. I have a criteria under
“Follow Up Date†as <Date()-1. This report is so that I can effectively
follow up on customers at designated dates. This works except it picks up
all “Notes†that match the criteria. The Problem is that I want only the
last or most rescent “Note†for each customer to be considered.

I have managed to pull the most recent dated record, but it was one record
(the most recent of all records), not the most recent record for each
company.

I keep trying Dmin or Demax expressions, but I cannot get it right. Can
someone point me in the right direction?

 

[Guest]

You want to Order By and Group on Company in your query. Also Order By the
FollowUpDate. Select Max(FollowUpDate) to see the latest FollowUpDate for
each Company based on your criteria.