Query to select records by latest date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am working with a training db and each time a module is refreshed by a person a new entry is made. I want to keep the history records in the table though I also want to be able to view current details for each person.

My table has the following fields
- ID for person
- Code for Module
- Assessment Date
- Frequency
- Expiration Date

I have set up a parameter query to select records for individuals, however, I don't know how to select the latest record by date for each of their modules completed?
 
Jan,

Change the query to a Totals Query (Click on the Sigma button on the toolbar)
and change Group By under Assessment Date to Max. Your query will always return
the most current record.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Jan H said:
I am working with a training db and each time a module is refreshed by a
Click to expand...
person a new entry is made. I want to keep the history records in the table
though I also want to be able to view current details for each person.
My table has the following fields
- ID for person
- Code for Module
- Assessment Date
- Frequency
- Expiration Date

I have set up a parameter query to select records for individuals, however, I
Click to expand...
don't know how to select the latest record by date for each of their modules
completed?
 
I think you need a Sub-Query. The SQL String should be
somthing like:

SELECT PersonID, ModuleCode, AssessmentDate,
Frequency, ExpirationDate
FROM YourTable As Main
WHERE
( Main.PersonID = [PersonID param] )
AND
( Main.AssessmentDate =
( SELECT Max(AssessmentDate)
FROM YourTable As Sub1
WHERE (Sub1.PersonID = Main.PersonID)
AND (Sub1.ModuleCode = Main.ModuleCode)
)
)

HTH
Van T. Dinh
MVP (Access)



-----Original Message-----
I am working with a training db and each time a module is
Click to expand...
refreshed by a person a new entry is made. I want to keep
the history records in the table though I also want to be
able to view current details for each person.
My table has the following fields
- ID for person
- Code for Module
- Assessment Date
- Frequency
- Expiration Date

I have set up a parameter query to select records for
Click to expand...
individuals, however, I don't know how to select the
latest record by date for each of their modules completed?
 
Back
Top