Sequential Record Use?

[bobahendrix]

Hello,
Let's say I have tblSubject, containing Subject ID's (SID), w/ a one to many
relationship
to
tblData,
which contains the Subject ID (tblSubject.SID), dates of blood testing
(tblData.Date),
and the test result (tblData.Result).

I want to create a query that gathers the above fields, and calculates the
sum of a record's [Result] and the [Result] of only the test JUST prior to it
(let's call that field [SumResult].

I can't figure out how to single out the previous record by the date...?
Thanks

 

[Bob Barrows]

Hello,
Let's say I have tblSubject, containing Subject ID's (SID), w/ a one
to many relationship
to
tblData,
which contains the Subject ID (tblSubject.SID), dates of blood testing
(tblData.Date),

I hope "Date" is not really the name of this field. "Date" is a reserved
keyword and should never be used as the name of a field (or any other
user-defined object). TestDate would be a much better name. I'm assuming
you will be able to rename it. If not, just correct the query I show you
below.

and the test result (tblData.Result).

I want to create a query that gathers the above fields, and
calculates the sum of a record's [Result] and the [Result] of only
the test JUST prior to it (let's call that field [SumResult].

I can't figure out how to single out the previous record by the
date...? Thanks

For which result? Let's assume you're interested in the latest and
penultimate test results. To get the latest 2 test results, you can use
the TOP keyword in a query where you are ordering by TestDate Desc. Then
you can add a group by and sum the result column. Like this:

SELECT SID,Sum(Result) As SumLastTwoResults
FROM (
Select TOP 2 s.SID, Result
FROM tblSubject As s JOIN
tblData As d ON s.SID=d.SID
ORDER BY TestDate DESC
) As q

 

[bobahendrix]

Awesome. That's just what I'm looking for. And yes, 'Date' was just an
example, my real table has different names.
Thanks!

Hello,
Let's say I have tblSubject, containing Subject ID's (SID), w/ a one
to many relationship
to
tblData,
which contains the Subject ID (tblSubject.SID), dates of blood testing
(tblData.Date),

I hope "Date" is not really the name of this field. "Date" is a reserved
keyword and should never be used as the name of a field (or any other
user-defined object). TestDate would be a much better name. I'm assuming
you will be able to rename it. If not, just correct the query I show you
below.

and the test result (tblData.Result).

I want to create a query that gathers the above fields, and
calculates the sum of a record's [Result] and the [Result] of only
the test JUST prior to it (let's call that field [SumResult].

I can't figure out how to single out the previous record by the
date...? Thanks

For which result? Let's assume you're interested in the latest and
penultimate test results. To get the latest 2 test results, you can use
the TOP keyword in a query where you are ordering by TestDate Desc. Then
you can add a group by and sum the result column. Like this:

SELECT SID,Sum(Result) As SumLastTwoResults
FROM (
Select TOP 2 s.SID, Result
FROM tblSubject As s JOIN
tblData As d ON s.SID=d.SID
ORDER BY TestDate DESC
) As q


--
HTH,
Bob Barrows


.

 

[Bob Barrows]

Good ... glad to hear it. Thanks for the feedback.

Awesome. That's just what I'm looking for. And yes, 'Date' was just
an example, my real table has different names.
Thanks!

Hello,
Let's say I have tblSubject, containing Subject ID's (SID), w/ a one
to many relationship
to
tblData,
which contains the Subject ID (tblSubject.SID), dates of blood
testing (tblData.Date),

I hope "Date" is not really the name of this field. "Date" is a
reserved keyword and should never be used as the name of a field (or
any other user-defined object). TestDate would be a much better
name. I'm assuming you will be able to rename it. If not, just
correct the query I show you below.

and the test result (tblData.Result).

I want to create a query that gathers the above fields, and
calculates the sum of a record's [Result] and the [Result] of only
the test JUST prior to it (let's call that field [SumResult].

I can't figure out how to single out the previous record by the
date...? Thanks

For which result? Let's assume you're interested in the latest and
penultimate test results. To get the latest 2 test results, you can
use the TOP keyword in a query where you are ordering by TestDate
Desc. Then you can add a group by and sum the result column. Like
this:

SELECT SID,Sum(Result) As SumLastTwoResults
FROM (
Select TOP 2 s.SID, Result
FROM tblSubject As s JOIN
tblData As d ON s.SID=d.SID
ORDER BY TestDate DESC
) As q


--
HTH,
Bob Barrows


.