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.
