Prev: Customizing the print view for a crosstab query
Next: Special Characters in Access 2007
From: sdg8481 on 21 Apr 2010 10:15
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: KARL DEWEY on 21 Apr 2010 11:06
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];

--
Build a little, test a little.


"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: sdg8481 on 21 Apr 2010 11:17
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: John W. Vinson on 21 Apr 2010 11:26
On Wed, 21 Apr 2010 07:15:01 -0700, sdg8481
<sdg8481(a)discussions.microsoft.com> 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

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 W. Vinson [MVP]
From: John Spencer on 21 Apr 2010 12:15
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
 |  Next  |  Last
Pages: 1 2
Prev: Customizing the print view for a crosstab query
Next: Special Characters in Access 2007