datediff for from two fields in same table, but separate records
I have one table that stores admission and discharge dates for patients admitted to our facility. I select a date range with parameters setting a start and end date for the report and ask to see all admissions for that range. One patient may be admitted several times in that timeframe so would show a record for each admission. I need a formula to calculate the time from the last discharge to the current admission. For example:
John Doe Admitted 5/1/08, discharged 6/1/08
John Doe Admitted 7/1/08, discharged 8/1/08
I am looking for any patients that were readmitted within 6 months of their last discharge. So am trying to get the datediff between the 7/1/08 admit and the previous record's 6/1/08 discharge date. I don't know how to write a formula to compare the two date fields from different records for the same patient.