Prev: Customizing the print view for a crosstab query
Next: Special Characters in Access 2007
From: sdg8481 on 21 Apr 2010 16:10
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" wrote:

> 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
>
> sdg8481 wrote:
> > 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" wrote:
> >
> >> 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
> .
>
From: KenSheridan via AccessMonster.com on 21 Apr 2010 16:59
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

sdg8481 wrote:
>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
>> .

--
Message posted via http://www.accessmonster.com

From: sdg8481 on 22 Apr 2010 04:31
Work great...thanks all

"KenSheridan via AccessMonster.com" wrote:

> 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
>
> sdg8481 wrote:
> >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
> >> .
>
> --
> Message posted via http://www.accessmonster.com
>
> .
>
From: John Spencer on 22 Apr 2010 09:16
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

KenSheridan via AccessMonster.com wrote:
> 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
>
> sdg8481 wrote:
>> 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
>>> .
>
First  |  Prev  | 
Pages: 1 2
Prev: Customizing the print view for a crosstab query
Next: Special Characters in Access 2007