Date Difference Calculation

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.

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


"Deb" wrote in message
...
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.

On Wed, 9 Nov 2005 12:48:01 -0800, Deb
wrote:

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.

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]

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

--
All help greatly appreciated.


"Tom Ellison" wrote:

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


"Deb" wrote in message
...
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.

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


"Deb" wrote in message
news
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

--
All help greatly appreciated.


"Tom Ellison" wrote:

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


"Deb" wrote in message
...
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.