Queries that return most recent data for every person.

[mnsemple83]

I designed a report that includes several subreports containing data relating to a patient. The report is created when the user selects a group of patients from a list, followed by a date value. The report will display all data pertaining to or before that date.



The subreports obtain their data from the queries, which uses the date value to compile the data. The data I want to focus on for each patient is their four most recent records. If the user selects 3 patients from the list, then enters 8/23/2011 as the date, each subreport will display the four most recent records as of that date.

The problem here is that the queries will return the four most recent records regardless of the patient. Instead of seeing 4 records for every patient, the reports will only contain a record for a patient if it's in the top 4, meaning not all of the patients will see their data.

I tried to supply the data from the list of patients selected to the queries but unfortunately, they only can process one value. I need to be able to redesign the queries so they return the 4 most recent entries for each patient and not just in general.

Any suggestions?

[mnsemple83]

Do not worry, I solved this problem.

All I had to do is add a little line of code inside each query that referred to the patient's ID, which is the matching value for each pair of tables involved in the query.

[Reports]![reportName]![nameofmatchingfield]