need to display last three dates for each patient

[dgb]

Hello,
I am trying to create a query that contains lab values for patients. I only want to display the last 3 dates for each patient. How do I do this.
Thank you, Dan

 

[Rob.Mills]

Are you saying three days older than the current day? If so, that would be:

>Date()-3

 

[dgb]

No.
For instance, pt A had lab results 1 month ago, 2 months ago and 10 months ago. Pt B had them 6 wks ago, 3 months ago and one year ago. So there is no pattern, but I want just the last 3 for each patient no matter when they were done.
Thank you

 

[Jon K]

Assuming the lab result dates are stored in a date/time field, try this in a column in the query grid (replacing with the correct table name, and the correct names of the patient field and the lab result date field):-

Field: (Select Count(*) from [TableName] as s where s.[Patient]=[TableName].[Patient] and s.[LabResultDate]>=[TableName].[LabResultDate])

Show: uncheck
Criteria: Between 1 and 3


Note: As a subquery (i.e. a Select query in brackets) is used, it will take time to run the query if the table contains many records.

 

[neileg]

You could use the Top Values property in your query. This will take the top n values of the leftmost column in the QBE grid. Althought the drop down doesn't offer a top 3 option, you can type in 3 and it works.

Look in Access help for top values.

 

[Jon K]

Neil

Would using top 3 return the last three records for each patient?

Jon

 

[neileg]

Jon, fair question. It depends if each patient means all patients or one patient at a time. It's slightly ambiguous, but perhaps your interpretation is correct.