Date Difference Calculation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Deb - 09 Nov 2005 20:48 GMT

Using Access 2003. Have a table with hospital admissions. Am trying to
retrieve records where a person has an admission within 30 days of another
admission. Each record has a unique identifier, each member has a member
number, and there is an admitdate field. I've tried a query with a date
difference but keep getting a "circular reference" msg. Can anyone help???

All help greatly appreciated.

Reply to this Message

Tom Ellison - 09 Nov 2005 21:41 GMT

Dear Deb:

Your information seem a bit sketchy to me. However, what you want is
something typically done with a correlated subquery.

If you will provide the SQL of what you are trying to do, perhaps I'd be
able to suggest some specific solution.

Tom Ellison

> Using Access 2003. Have a table with hospital admissions. Am trying to
> retrieve records where a person has an admission within 30 days of another
[quoted text clipped - 4 lines]
>
> All help greatly appreciated.

Reply to this Message

Deb - 10 Nov 2005 00:37 GMT

Tom, I was trying to do this within a query. Here is an example of the data
in the table:

Admit# MbrID AdmitDate
123 456 1/1/05
789 1011 1/15/05
321 456 1/28/05
654 456 3/15/05

For member ID 456 I'd like the query to return the admits on 1/1/05 and
1/28/05 as they are 30 or less days apart. Hope this helps you help me!!

Deb

Signature

All help greatly appreciated.

> Dear Deb:
>
[quoted text clipped - 14 lines]
> >
> > All help greatly appreciated.

Reply to this Message

Tom Ellison - 10 Nov 2005 04:15 GMT

Dear Deb:

Perhaps this is a starting point:

SELECT T.Admit#, T.MbrID, T.AdmitDate AS CurrentAdmitDate,
MAX(T1.AdmitDate) AS PrevAdmitDate
FROM YourTable T
INNER JOIN YourTable T1 ON T1.MbrID = T.MbrID
WHERE T1.AdmitDate < T.AdmitDate
AND DateDiff("d", T.AdmitDate, T1.AdmitDate) <= 30
GROUP BY T.Admit#, T.MbrID, T.AdmitDate

You need to replace YourTable with the actual name of the table involved.
Do not make any other changes for now.

Examine these results carefully. There's a lot of possibility for missed
communications or assumptions I may have made.

Some things to look for are that a member may have more than one pair of
admittances, in which case that member will show up more than once, and that
a member could have been admitted more than once during any 30 day period.
The query will handle these cases in a consistent manner, but that may not
be the manner you hope for. Creating rather stressful test data to observe
all such behaviors would be a good idea. This is just complex enough to
warrant some real care, and our communication has not been all that thorough
respecting all possible cases.

Tom Ellison

> Tom, I was trying to do this within a query. Here is an example of the
> data
[quoted text clipped - 33 lines]
>> >
>> > All help greatly appreciated.

Reply to this Message

John Vinson - 09 Nov 2005 23:13 GMT

>Using Access 2003. Have a table with hospital admissions. Am trying to
>retrieve records where a person has an admission within 30 days of another
[quoted text clipped - 3 lines]
>
>All help greatly appreciated.

Guessing at your table structure here but...

Try a Self Join query. Add the Admissions table to the query grid
TWICE. Join the two instances by Member Number.

Put a criterion on the second instance's unique identifier of

<> [tablename].[unique identifier]

using your tablename and unique identifier fieldname of course.

Include a calculated field

DateGap: DateDiff("d", [tablename].[admitdate],
[tablename_1].[admitdate])

using the table name and the alias that Access creates for the second
instance of the table.

Put a criterion on this field of

<=30

John W. Vinson[MVP]

Reply to this Message