Compare dates in two seperate records

[sdg8481]

Hi,

I have a table that has multiple rows for person, each with a different
admission date. What need to do is to build some a query that will identify
the number of days between each record, is this possible?

Originally, simply did a difference between the min admission date and the
maximum admission date. However, this only works where two records exist, and
i need to it calculate the difference for say 4 birth records. eg: the number
of days between birth 1-2,2-3,3-4,1-3,1-4,2-4...etc...

Hope this makes sense.

thanks

 

[KARL DEWEY]

Try this --
SELECT [YourTable ].[AdmissionDate], DateDiff("d",(SELECT TOP 1 [XX].[
AdmissionDate] FROM YourTable AS [XX] WHERE [XX].[ AdmissionDate] >
[YourTable ].[AdmissionDate] ORDER BY [XX].[ AdmissionDate]), [YourTable
].[AdmissionDate]) AS [Days between birth]
FROM YourTable
ORDER BY [YourTable].[ AdmissionDate];

 

[sdg8481]

Thank you for your reply.

I converted your vba code into my database, as follows:

SELECT [T04_Working Cohort (Last submission per admission) ].[Admission
Date] AS Expr1, DateDiff("d",(SELECT TOP 1 [XX].[Admission Date] FROM
[T04_Working Cohort (Last submission per admission)] AS [XX] WHERE
[XX].[Admission Date] >
[T04_Working Cohort (Last submission per admission)].[Admission Date] ORDER
BY [XX].[Admission Date]),[T04_Working Cohort (Last submission per
admission)].[Admission Date]) AS [Days between birth]
FROM [T04_Working Cohort (Last submission per admission)]
ORDER BY [T04_Working Cohort (Last submission per admission)].[Admission
Date];

But unfortunately when i run this it gives me the message ; At most one
record can be returned by this subquery"

What am i doing wrong.

Thanks

 

[John W. Vinson]

Hi,

I have a table that has multiple rows for person, each with a different
admission date. What need to do is to build some a query that will identify
the number of days between each record, is this possible?

Originally, simply did a difference between the min admission date and the
maximum admission date. However, this only works where two records exist, and
i need to it calculate the difference for say 4 birth records. eg: the number
of days between birth 1-2,2-3,3-4,1-3,1-4,2-4...etc...

Hope this makes sense.

thanks

You'll need some sort of a subquery. It would help a lot if you would post the
actual fieldnames and structure of your table.

 

[John Spencer]

You might try the following SQL statement.

You can use an expression like the following to get the prior date.
DMax("[Admission Date]"
,"[T04_Working Cohort (Last submission per admission)]"
,"[Admission Date]<=#" & [Admission Date] & "#")

You probably need to expand the last argument to that to limit it to one
specific person. So if you have a personId that is a number field you might
end up with something like the following.

DMax("[Admission Date]"
,"[T04_Working Cohort (Last submission per admission)]"
,"[Admission Date]<=#" & [Admission Date] & "# AND PersonID=" & [PersonID])

IF PersonId is a text string then it will look more like this:

DMax("[Admission Date]"
,"[T04_Working Cohort (Last submission per admission)]"
,"[Admission Date]<=#" & [Admission Date] & "# AND PersonID=""" & [PersonID] &
"""")

Your final query would be something like the following.

SELECT [Admission Date]
, DateDiff("d",<<one of the expressions>>, [Admission Date])
AS [Days between birth]
FROM [T04_Working Cohort (Last submission per admission)]
ORDER BY [Admission Date];

IF you have a large number of records this may be too slow. In that case,
post back with a little more information on your table and field structure. I
will try to post a faster but more complex solution.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Thank you for your reply.

I converted your vba code into my database, as follows:

SELECT [T04_Working Cohort (Last submission per admission) ].[Admission
Date] AS Expr1, DateDiff("d",(SELECT TOP 1 [XX].[Admission Date] FROM
[T04_Working Cohort (Last submission per admission)] AS [XX] WHERE
[XX].[Admission Date] >
[T04_Working Cohort (Last submission per admission)].[Admission Date] ORDER
BY [XX].[Admission Date]),[T04_Working Cohort (Last submission per
admission)].[Admission Date]) AS [Days between birth]
FROM [T04_Working Cohort (Last submission per admission)]
ORDER BY [T04_Working Cohort (Last submission per admission)].[Admission
Date];

But unfortunately when i run this it gives me the message ; At most one
record can be returned by this subquery"

What am i doing wrong.

Thanks

Hi,

I have a table that has multiple rows for person, each with a different
admission date. What need to do is to build some a query that will identify
the number of days between each record, is this possible?

Originally, simply did a difference between the min admission date and the
maximum admission date. However, this only works where two records exist, and
i need to it calculate the difference for say 4 birth records. eg: the number
of days between birth 1-2,2-3,3-4,1-3,1-4,2-4...etc...

Hope this makes sense.

thanks

 

[sdg8481]

hi, thank you for your detailed response, this seem to do the trick. However,
as you suggested this is very slow and actaully only runs one in three times
(other times the DB falls over)

The table itself is a flat table that contains in excess of 600,000 records.
The key fields are;

pID - a unique reference number for each person, this is a text field
Admission Date - the field that contains the date fields i need to compare

If it helps there is also a autonumber (ROWID) in use which is a unique
numerical value per record.

As i mentioned i need to, for each pID, report the date difference between
the admission date for multiple records that each patient may have. With the
ultimate aim of flagging any ROWID's where the previous record for that
patient is within 100 days.

Hope this makes sense, your're all genuis

You might try the following SQL statement.

You can use an expression like the following to get the prior date.
DMax("[Admission Date]"
,"[T04_Working Cohort (Last submission per admission)]"
,"[Admission Date]<=#" & [Admission Date] & "#")

You probably need to expand the last argument to that to limit it to one
specific person. So if you have a personId that is a number field you might
end up with something like the following.

DMax("[Admission Date]"
,"[T04_Working Cohort (Last submission per admission)]"
,"[Admission Date]<=#" & [Admission Date] & "# AND PersonID=" & [PersonID])

IF PersonId is a text string then it will look more like this:

DMax("[Admission Date]"
,"[T04_Working Cohort (Last submission per admission)]"
,"[Admission Date]<=#" & [Admission Date] & "# AND PersonID=""" & [PersonID] &
"""")

Your final query would be something like the following.

SELECT [Admission Date]
, DateDiff("d",<<one of the expressions>>, [Admission Date])
AS [Days between birth]
FROM [T04_Working Cohort (Last submission per admission)]
ORDER BY [Admission Date];

IF you have a large number of records this may be too slow. In that case,
post back with a little more information on your table and field structure. I
will try to post a faster but more complex solution.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Thank you for your reply.

I converted your vba code into my database, as follows:

SELECT [T04_Working Cohort (Last submission per admission) ].[Admission
Date] AS Expr1, DateDiff("d",(SELECT TOP 1 [XX].[Admission Date] FROM
[T04_Working Cohort (Last submission per admission)] AS [XX] WHERE
[XX].[Admission Date] >
[T04_Working Cohort (Last submission per admission)].[Admission Date] ORDER
BY [XX].[Admission Date]),[T04_Working Cohort (Last submission per
admission)].[Admission Date]) AS [Days between birth]
FROM [T04_Working Cohort (Last submission per admission)]
ORDER BY [T04_Working Cohort (Last submission per admission)].[Admission
Date];

But unfortunately when i run this it gives me the message ; At most one
record can be returned by this subquery"

What am i doing wrong.

Thanks

Hi,

I have a table that has multiple rows for person, each with a different
admission date. What need to do is to build some a query that will identify
the number of days between each record, is this possible?

Originally, simply did a difference between the min admission date and the
maximum admission date. However, this only works where two records exist, and
i need to it calculate the difference for say 4 birth records. eg: the number
of days between birth 1-2,2-3,3-4,1-3,1-4,2-4...etc...

Hope this makes sense.

thanks

.

 

[KenSheridan via AccessMonster.com]

Try this:

SELECT A1.pID, A1.AdmissionDate,
A1.AdmissionDate - MAX(A2.AdmissionDate)
AS DaysSinceLastAdmissionDate
FROM Admissions as A1 INNER JOIN Admissions As A2
ON A1.pID = A2.pID
AND A2.AdmissionDate < A1.AdmissionDate
GROUP BY A1.pID, A1.AdmissionDate
ORDER BY A1.pID, A1.AdmissionDate DESC;

Or, extending it to return those rows where the interval is 100 days or less:

SELECT A1.pID, A1.AdmissionDate,
A1.AdmissionDate - MAX(A2.AdmissionDate)
AS DaysSinceLastAdmissionDate
FROM Admissions as A1 INNER JOIN Admissions As A2
ON A1.pID = A2.pID
AND A2.AdmissionDate < A1.AdmissionDate
GROUP BY A1.pID, A1.AdmissionDate
HAVING A1.AdmissionDate - MAX(A2.AdmissionDate) <= 100
ORDER BY A1.pID, A1.AdmissionDate DESC;

Ken Sheridan
Stafford, England

hi, thank you for your detailed response, this seem to do the trick. However,
as you suggested this is very slow and actaully only runs one in three times
(other times the DB falls over)

The table itself is a flat table that contains in excess of 600,000 records.
The key fields are;

pID - a unique reference number for each person, this is a text field
Admission Date - the field that contains the date fields i need to compare

If it helps there is also a autonumber (ROWID) in use which is a unique
numerical value per record.

As i mentioned i need to, for each pID, report the date difference between
the admission date for multiple records that each patient may have. With the
ultimate aim of flagging any ROWID's where the previous record for that
patient is within 100 days.

Hope this makes sense, your're all genuis

You might try the following SQL statement.

[quoted text clipped - 72 lines]

.

 

[sdg8481]

Work great...thanks all

Try this:

SELECT A1.pID, A1.AdmissionDate,
A1.AdmissionDate - MAX(A2.AdmissionDate)
AS DaysSinceLastAdmissionDate
FROM Admissions as A1 INNER JOIN Admissions As A2
ON A1.pID = A2.pID
AND A2.AdmissionDate < A1.AdmissionDate
GROUP BY A1.pID, A1.AdmissionDate
ORDER BY A1.pID, A1.AdmissionDate DESC;

Or, extending it to return those rows where the interval is 100 days or less:

SELECT A1.pID, A1.AdmissionDate,
A1.AdmissionDate - MAX(A2.AdmissionDate)
AS DaysSinceLastAdmissionDate
FROM Admissions as A1 INNER JOIN Admissions As A2
ON A1.pID = A2.pID
AND A2.AdmissionDate < A1.AdmissionDate
GROUP BY A1.pID, A1.AdmissionDate
HAVING A1.AdmissionDate - MAX(A2.AdmissionDate) <= 100
ORDER BY A1.pID, A1.AdmissionDate DESC;

Ken Sheridan
Stafford, England

hi, thank you for your detailed response, this seem to do the trick. However,
as you suggested this is very slow and actaully only runs one in three times
(other times the DB falls over)

The table itself is a flat table that contains in excess of 600,000 records.
The key fields are;

pID - a unique reference number for each person, this is a text field
Admission Date - the field that contains the date fields i need to compare

If it helps there is also a autonumber (ROWID) in use which is a unique
numerical value per record.

As i mentioned i need to, for each pID, report the date difference between
the admission date for multiple records that each patient may have. With the
ultimate aim of flagging any ROWID's where the previous record for that
patient is within 100 days.

Hope this makes sense, your're all genuis

You might try the following SQL statement.

[quoted text clipped - 72 lines]

thanks
.

 

[John Spencer]

Thanks Ken. Exactly what I would have proposed if I had not been lazy.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Try this:

SELECT A1.pID, A1.AdmissionDate,
A1.AdmissionDate - MAX(A2.AdmissionDate)
AS DaysSinceLastAdmissionDate
FROM Admissions as A1 INNER JOIN Admissions As A2
ON A1.pID = A2.pID
AND A2.AdmissionDate < A1.AdmissionDate
GROUP BY A1.pID, A1.AdmissionDate
ORDER BY A1.pID, A1.AdmissionDate DESC;

Or, extending it to return those rows where the interval is 100 days or less:

SELECT A1.pID, A1.AdmissionDate,
A1.AdmissionDate - MAX(A2.AdmissionDate)
AS DaysSinceLastAdmissionDate
FROM Admissions as A1 INNER JOIN Admissions As A2
ON A1.pID = A2.pID
AND A2.AdmissionDate < A1.AdmissionDate
GROUP BY A1.pID, A1.AdmissionDate
HAVING A1.AdmissionDate - MAX(A2.AdmissionDate) <= 100
ORDER BY A1.pID, A1.AdmissionDate DESC;

Ken Sheridan
Stafford, England

hi, thank you for your detailed response, this seem to do the trick. However,
as you suggested this is very slow and actaully only runs one in three times
(other times the DB falls over)

The table itself is a flat table that contains in excess of 600,000 records.
The key fields are;

pID - a unique reference number for each person, this is a text field
Admission Date - the field that contains the date fields i need to compare

If it helps there is also a autonumber (ROWID) in use which is a unique
numerical value per record.

As i mentioned i need to, for each pID, report the date difference between
the admission date for multiple records that each patient may have. With the
ultimate aim of flagging any ROWID's where the previous record for that
patient is within 100 days.

Hope this makes sense, your're all genuis

You might try the following SQL statement.

[quoted text clipped - 72 lines]

thanks
.