Record Selection

[surfbum3000]

I need to create a query that will select a DR. APPT. as the primary appointment when a patient has more than one appt. with a provider. The tblAppts contains the following fields:

CASE_NUM DATE SVC_ID SUBJECT STARTTIME
12345 08/17/2004 1000 C.W. APPT. 09:00 (Delete this appt.)
12345 08/17/2004 2000 NURSE APPT. 09:30 (Delete this appt.)
12345 08/17/2004 3000 DR. APPT. 10:00

Here is another example:

CASE_NUM DATE SVC_ID SUBJECT STARTTIME
12345 08/17/2004 3000 DR. APPT. 09:00
12345 08/17/2004 2000 NURSE APPT. 09:30 (Delete this appt.)
12345 08/17/2004 1000 C.W. APPT. 10:00 (Delete this appt.)

 

[BNPMike]

It's not clear what you're asking. The term 'primary appointment' is not intuitively obvious. Why delete other appointments just because you are also going to see a doctor?

However is this your question: "How do you delete non-DR. APPT. records for a given patient for a given day if on that day for that patient there is a DR.APPT record?"?

 

[amorous]

And why there is need to delete records that are not for Dr.Appt

you can write a query just to show the record which has Dr.Appt for any particular patient on any particular day...


-VJ

 

[surfbum3000]

Sorry for not making my request clear. There numerous case numbers in the table and some case numbers have multiple appointments. For instance, case number 1 might have a Dr. appt., Nurse appt., and a Caseworker appt. Case number 2 might have a nurse appt. only. I know how to query for a Dr. appt. only, but if I do that, I will lose the case number with the nurse appt. only. The software used to call patients with reminder calls only calls the 1st appt. in time. The current complaint is the Dr. appt. is not given priority as a reminder call, which will be true if other appts. precede it.

So, if the pt. has multiple appts. in one day with mutiple providers and one of them is a Dr. appt., select the Dr. appt. only. If there is no Dr. appt. that day, no action is necessary.

 

[BNPMike]

So in this case you choose the 09:00 appointment?

CASE_NUM DATE SVC_ID SUBJECT STARTTIME
13333 08/18/2004 1000 C.W. APPT. 09:00
13333 08/18/2004 2000 NURSE APPT. 09:30

 

[surfbum3000]

So in this case you choose the 09:00 appointment?

The answer is Yes.

 

[surfbum3000]

Perhaps this will help. If a patient has more than one appointment, and one of them is a Dr. appt., then the Dr. appt. is the only one selected, regardless if it occurs after the other appts.

For all multiple appts. that are not with a Dr., then the 1st appt. in time is to be selected.