Most Recent Record of Several Individuals

[JJOHNS]

I have a program in which members are assessed monthly. The problem is not all members are assessed on the same day, and some less active members occasionally skip a month. My database tracks the assessments. I need a report that list all the members and their most recent assessment.

Can someone help me figure out how to do this?

Thanks

 

[HitechUser]

JJOHNS,

Can you provide some table layout information??

What version of Access??

...

 

[dhookom]

I am having a little trouble seeing your table, fields, and data. I expect others are too. Do you mind helping us out a little?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JJOHNS]

Here is what my assessment table is set up like:

PKey - Name
PKey - AssessmentDate
Result1
Result2

The people in this program are assessed based on certain results. It's a health-monitoring program, so they're assessed based on weight change, heart rate, BP, etc. Each month's assessments are entered into the same table, but I want the report to pull only the most recent assessment of each person.

 

[dhookom]

The simplest method is to create a totals query
SELECT [Name], Max(AssessmentDate) as MaxDate
FROM tblAssessment
GROUP BY [Name];
Save this query as qgrpNameDate and then create a new query with the table and the new query. Join the [Name] fields and AssessmentDate with MaxDate.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JJOHNS]

The problem with that is that because the name field is in the query, I get this error:

"You tried to execute a query that does not include the specified expression '[Name]' as part of an aggregate function."

I'm starting to think the best answer would be to create a second table. I could delete the last assessment for the person from one table, but not the other. Then I could insert the current assessment record into both tables. That'll work, but it just seems like there's got to be an easier way.

 

[dhookom]

Which query gives you the error? Could you provide the SQL view of both queries?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]