Comparing dates in two different tables to determine which date and data associated with it to use

[Ctrl-v]

Hi,



This is what I am doing and what I am looking for. My starting point may also be wrong, so let me know.

I am looking to produce to list. One has a list of patients that are discharged. The other is a list of patients that are enrolled.

The enrolled and discharged are on two separate forms. When someone is enrolled, their enrollment status and date of the status is put on one form. When someone is discharged, their discharge status and date of discharge are put on another form.

Each patient may be enrolled, discharged, and then enrolled again, and then discharged, and so on.

I want the list of discharged and the list of enrolled to reflect the last date of each patient.

For example:

Patient A has enrollment dates of 10/2/2018, 11/5/2018, and 12/8/2018.

Patient A also has discharge dates and the discharge table and they are 10/23/2018 and 11/19/2018.

Therefore, I want patient A to show up on the enrolled query, because the latest of the above dates is an enrollment date.


At first I was going to put them both on one table and one of the cells in the table would determine if the date is a discharge date or enrollment date. I had a difficulty with doing that because the discharge would be edited with a discharge form, and I did not want to discharge form to also pull data from the enrollment rows. So unless there is a simple way to make the discharge form not pick up the enrollment data (maybe some filter to filter the enrollment data), then I still have my question.

Thank you!!!

[JoeM]

You can do an Aggregate Query for each of your tables to pull the latest date for each person, i.e.

SELECT PatientID, Max(EnrollDate)
FROM EnrollTable
GROUP BY PatientID

and

SELECT PatientID, Max(DischargeDate)
FROM DischargeTable
GROUP BY PatientID

You can then join those two queries together in another query, joining on the PatientID field, and return the latest Enrollment and Discharge dates and compare them.

Note, I would probably recommend doing a LEFT OUTER JOIN from the Enroll Query to the Discharge Query, because it may be possible that someone has one enrollment and zero discharges (they haven't been discharged yet).
That way you won't lose any records. You can use the Nz function on the MaxDischargeDate field to handle those nulls.

[Ctrl-v]

Thank you for your help. I'm very new to Access, and I'm running in to some issues.

First I made the enrollment query. It seems to work fine, but I also want to include other columns so that I can still pull other peices of information regarding the result, but I am having difficulty with it. Once I had in another column to get other info, it gives me too many records; it is not exclusively giving me the records for the max of each ID.

What should I do?

[JoeM]

First I made the enrollment query. It seems to work fine, but I also want to include other columns so that I can still pull other peices of information regarding the result, but I am having difficulty with it. Once I had in another column to get other info, it gives me too many records; it is not exclusively giving me the records for the max of each ID.

You need to be careful when doing aggregate queries. You do not want to do it that way.

Here is the way Aggregate Queries work. You have one or more fields that you are "Grouping By". So, it says combine all records where these fields match into a single group (record). Then, you have your Aggregate function (i.e. Count, Max, Last, etc), which is typically only applied to one field. If you add other fields to this query, and they are "Group By", they will be considered in your grouping. Adding fields usually leads to an increase in the number of groups that you may (breaks it down further than you want to go).

Let's say that you add another field named "Location". So, now instead of returning the latest date for person "Bob", you are now returning the latest dates for "Bob" at each "Location" he has gone to.

If you want to get other values from that Max date record, what you want to do is set it up exactly as I have shown you. Then create another query which joins this Aggregate Query back to the original data table, joining on BOTH the Patient and Date fields. Then you can return the other fields from the original table in this query (and they will be associated with the proper record).

[Ctrl-v]

Okay. Thank you for the in depth explanation!

[JoeM]

You are welcome!