calculate number of months from baseline date

[airnuminz]

On the many side of a one-to-many, my many table looks something like
this:

(The " --- " is just to help you see when a new patient begins)

###

PatientID VisitNo VisitDate MonthsFromVisitZero
1 0 5/1/03 0
1 1 6/1/03 1
1 2 9/1/03 4
1 3 2/1/04 9
----
2 0 1/1/02 0
2 1 5/1/02 4
----
3 0 3/1/05 0
3 1 1/1/06 10
3 2 2/1/06 11
3 3 4/1/06 13

###

But MonthsFromVisitZero is currently blank (those are the values that
*should* be there). How can I create a query that calculates the value
for MonthsFromVisitZero. As I see it, for each visit, for each
patient, I need to subtract VisitDate from (VisitDate WHERE VisitNo =
0). I just can't figure out the syntax.

Thanks!

 

[Michel Walsh]

SELECT a.patientID, a.visitNo, a.visitDate, DateDiff("m", b.visitDate,
a.VisitDate)

FROM tableNameHere AS a INNER JOIN
(SELECT patientID, visitDate FROM tableNameHere WHERE visitNo=0) AS b
ÒN a.patientID = b.patientID

ORDER BY a.patientID, a.visitNo



The virtual table picks up only the records where visitNo=0. There is
(assumed) to be only one record, per patientID, with such a record, so,
DateDiff produces the desired result, given the INNER JOIN imposed condition
(a.patientID=b.patientID).



Hoping it may help,
Vanderghast, Access MVP

 

[airnuminz]

SELECT a.patientID, a.visitNo, a.visitDate, DateDiff("m", b.visitDate,
a.VisitDate)

FROM tableNameHere AS a INNER JOIN
(SELECT patientID, visitDate FROM tableNameHere WHERE visitNo=0) ASb
ÒN a.patientID = b.patientID

ORDER BY a.patientID, a.visitNo

The virtual table picks up only the records where visitNo=0. There is
(assumed) to be only one record, per patientID, with such a record, so,
DateDiff produces the desired result, given the INNER JOIN imposed condition
(a.patientID=b.patientID).

Hoping it may help,
Vanderghast, Access MVP








- Show quoted text -

THANKS!! IT WORKED!!