Comparing data across records and returning value

[foxtrot123]

Hope I'm not wearing out my welcome. Just suddenly facing a lot of tricky queries that are boggling my mind. Any advice or tips much appreciated.

I have data on surgeries. A patient goes in and gets one or more metal implants. I also have a flag to indicate his first surgery.

If the patient comes in for a subsequent surgery, and has more implants added, I need to flag his first surgery as "Needed More" and his subsequent surgery as "Got More". This occurs with Pts 2 and 3.

Original Data

PtID  SurgDate FirstSurg SurgID  ImplantsAdded
1     1/1/08   Yes       1        1
1     5/1/09             2                  
--------------------------------------------------------
2     1/1/07   Yes       3        2
2     5/1/07             4        1         
--------------------------------------------------------
3     1/1/07   Yes       5        1
3     5/1/07             6                
3     7/1/07             7        1

So the query would return:

New Data

PtID  SurgDate FirstSurg SurgID  ImplantsAdded  Status
1     1/1/08   Yes       1        1              
1     5/1/09             2                  
--------------------------------------------------------
2     1/1/07   Yes       3        2              Needed More
2     5/1/07             4        1              Got More
--------------------------------------------------------
3     1/1/07   Yes       5        1              Needed More
3     5/1/07             6                
3     7/1/07             7        1              Got More

Any suggestions? It seems the logic goes like this:

Within PtID, for the first surgery (FirstSurg - Yes), if ImplantsAdded >= 1, and a subsequent surgery has ImplantsAdded >=1 (subsequent SurgDate > the previous SurgDate) AND (ImplantsAdded =>1), then:

Status for previous surgery = "NeededMore"
Status for subsequent surgery = "GotMore"

Any suggestions about how to tackle this?

 

[plog]

Make a sub-query that counts the number of times a patient had Implants Added:

SELECT PTID, COUNT(PTID) AS ImplantTimes 
FROM [Original Data]
WHERE IsNull(ImplantsAdded)=False
GROUP BY PTID;

Then, create a new query based on your Original Data and LEFT JOIN to that query. Create a status field that looks to see if the current record had ImplantsAdded, if so spit out your status message based on if FirstSurg is Yes and the value in the ImplantTimes field.

Status: Iif(IsNnull(ImplantsAdded)=False AND ImplantTimes>1, IIf(FirstSurg='Yes', "Needed More", "Got More"))

 

[foxtrot123]

Just wanted to reply to say, once again, your query worked great. Much thanks.