Assistance with selecting first visit record of patients

Hi all

I saw a similar query on another thread but was unable to use the answers to resolve my problem.

I have an access database with two tables - the first contains demographic data for patients (Initial visit table) and the second (followup visit) contains all visit records for these patients linked by a PID. Some of the data was entered retrospectively so the record number is not a reflection of date of visit. Have 25000 visits captured.

I need to devise a query to extract the first CD4 count (a blood result) available for each patient - cd4 not done on every visit. Have tried using:

SELECT InitialVisitID, VisitDatetime, CD4CountPercentage

FROM [FollowUpVisit] AS a

WHERE (((a.InitialVisitID)=(select top 1 InitialVisitID from [FollowUpVisit]b

where a.InitialVisitID = b.InitialVisitID

order by VisitDatetime desc)));

This initially generates a table with duplicates followed shortly thereafter by a warning that only one record can be returned by the subquery and then blanking out of all records in the query output.

Please help!

I believe you want something like this:

SELECT a.InitialVisitID, a.VisitDatetime, a.CD4CountPercentage

FROM FollowUpVisit a

inner join

(select Min(InitialVisitID) as InitialVisitID, VisitDatetime, CD4CountPercentage

from FollowUpVisit

group by InitialVisitID

) as b

on a.InitialVisitID = b.InitialVisitID

order by a.VisitDatetime desc

|||

Try:

SELECT InitialVisitID, VisitDatetime, CD4CountPercentage

FROM [FollowUpVisit] AS a

WHERE (((a.InitialVisitID)=(select top 1 InitialVisitID from [FollowUpVisit] b

where b.PID = a.PID

order by VisitDatetime desc)));

AMB

|||

Thanks for the quick response AMB

Your suggestion has the same result - except asks for the PID paramter to be entered.

Just for clarification InitialVisitID in this database is in fact the PID. So i tried it with changing PID to InitialVisitID but same outcome.

h

|||

Thanks for the response

For clarification the "InitialVisitId" is the PID - so selecting for min InitialVisitID does not work as every patient only has one InitialVisitID (is unique identifier and primary key). Your solution also gives a circular reference problem within the Selection list. Tried making it:

SELECT a.InitialVisitID, a.VisitDatetime, a.CD4CountPercentage
FROM FollowUpVisit a
inner join
(select Min(VisitDateTime) as VisitDateTime, InitialVisitID, CD4CountPercentage
from FollowUpVisit
group by InitialVisitID
) as b
on a.InitialVisitID = b.InitialVisitID
order by a.VisitDatetime desc

but got same problem

h

|||

Please, do not make us to guess your enviroment. Post some DDL, including constraints and indexes, sample data and expected result.

Can you post the error msg you are getting?. I do not think that (select top 1 c1, ..., cn from ... order by) can bring more than one row, without using keywords WITH TIES.

AMB

|||

I am confused by this table FollowUpVisit. If this was a Visit table, I might understand, but is the InitialVisit stored in the FollowUpVisit table?

And this seems to imply that you can have >1 InitialVisit?

In your query, be sure and use aliases for every column, just to be careful with the output:

SELECT a.InitialVisitID, a.VisitDatetime, a.CD4CountPercentage

FROM [FollowUpVisit] AS a

WHERE (((a.InitialVisitID)=(select top 1 b.InitialVisitID from [FollowUpVisit] b

where a.InitialVisitID = b.InitialVisitID

order by b.VisitDatetime desc)));

I agree with Hunchback, in that I don't know how that top 1 query can return > 1 row.