Compare admission date to previous discharge date, VBA (1 Viewer)

[Lifeseeker]

Hi,

I have attached a sample database. Basically I want to have some lines of code that generate the result table, which is tbl_readmit_result.

As you can see, the difference is the addition of a new column called re_admit_status.

Rule is:

Status = "y" when the admission date, compared to the previous discharge is less than 7 days, otherwise "n", for the same pt_id. You cannot compare the two dates on different pt_ids.

Is there a way that this can be done automatically without having to go through the record manually?

Any help is appreciated.

 

[PeterF]

A query with a calculated field can do this like:

SELECT tbl_visit.ID, tbl_visit.admit, tbl_visit.disch, tbl_visit.pt_ID, IIf(DateDiff("d",[admit],DMax("disch","tbl_visit","admit <" & "#" & Format([admit],"mm\/dd\/yyyy") & "#"))<7,"y","n") AS re_admit_status
FROM tbl_visit 
ORDER BY tbl_visit.admit;;

The SQL output differs from your desired output in row 8, your admit date is in 2003.