Compare dates in two seperate records

S

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
 
K

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];
 
S

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
 
J

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.
 
J

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

sdg8481 said:
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
 
S

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

John Spencer said:
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

sdg8481 said:
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
.
 
K

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]
 
S

sdg8481

Work great...thanks all

KenSheridan via AccessMonster.com said:
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]
 
J

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]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top