Unanswered: Please help with a flat file query question
I need some desperate help with a report I'm working on. Let me try to explain what I'm going after. Basically, I have a table patient appointments. The goal is to show the last appointment for each patient and not show anything prior.
I am working with a flat file in access and this is what I have accomplish so far. I have sorted and group the table in such a way that the last appointment is always on the top line and all same patients are group together by their medical record numbers.
My only question is how do I get rid of all the prior appointments (lines below for the same patient) for the same patient? On last note, it is possible that there is only one appointment for one patient. In that case just leave that line.
Attach is a screenshot of my query thus far. Please help!!
I am not sure if you want only one record or the last appointment for EACH patient.
If the latter, then the easiest way (and easiest to explain) is to construct another query to return the latest date and MRN for each patient and then add this query to the existing query with an INNER JOIN on LastDate = [APP DATE]) and MRN = MRN.
The new query would be something like
SELECT Max([APP DATE]) as LastDate, MRN From CUMMULATIVE GROUP BY MRN;
Is that what you had in mind ??
You can also use a similar query as a sub query with the IN predicate in the Criteria field for the [APP DATE] field in the existing query, but relating it the existing table etc. is a little more difficult to understand (and explain) if you are not to familiar with SQL.
BTW I know you know it’s a flat file but it really shouldn’t be. If you want to change it to a normalised state then do it sooner rather than later.