Query with date criteria

[Deb Wolney]

Using Access 2000. I have a table with start and end
dates for hospital admissions. I want to run a query
which will return only those records for people whose end
date is <=30 days of the previous record's start date.
Example:

Member Start Dt End Date
Member 1 12/1/02 12/15/02
Member 1 12/29/02 12/31/02
Member 1 8/10/02 8/12/02
Member 2 4/1/02 4/5/02
Member 2 7/15/02 7/18/02

Query would return the first two of Member 1's records and
none of Member 2's.

All help much appreciated.

 

[Brian Camire]

You might try a query whose SQL looks something like this:

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
(SELECT
Count(*)
FROM
[Your Table] AS [Self]
WHERE
[Self].[Member] = [Your Table].[Member]
AND
[Self].[End Date] >= DateAdd("d", -30, [Your Table].[Start Dt])
AND
[Self].[Start Dt] <= DateAdd("d", 30, [Your Table].[End Date])) > 1

This assumes your table is named "Your Table"