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

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.

 

[dhookom]

Do you actually want to delete appointments? If so create a delete query where the Subject field is not like "DR.*"

I hope you understand what you are doing...

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ineuw]

This can be done in 2 queries. The 1st checks for a doctor appointment and if there is one, the 2nd deletes the unwanted appointments as in CASE_NUM = 12345 AND APPOINTMENT_DATE = DATE (AND APPOINTMENT_TIME = STARTTIME) AND SUBJECT <> DR. APPT.

Win2000P/Acc2000 - It's best to stay with products that work.

 

[dhookom]

Good catch on the "more than one appt". I was a bit off with my response.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[PHV]

Something like this ?
SELECT A.* FROM tblAppts A INNER JOIN
(SELECT TOP 1 IIf(P.SUBJECT="DR. APPT.",0,P.STARTTIME) As Priority,P.CASE_NUM,P.DATE,P.STARTTIME
FROM tblAppts P WHERE P.CASE_NUM=A.CASE_NUM And P.DATE=A.DATE ORDER BY 1) T
ON (A.CASE_NUM=T.CASE_NUM) And (A.DATE=T.DATE) And (A.STARTTIME=T.STARTTIME)
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[surfbum3000]

Actually, after I thought about it, I really don't need to delete the non-Dr. appts. The query will take care of not including those appts. in the final result. I tried this code:

SELECT A.*
FROM tblAppts AS A INNER JOIN [SELECT TOP 1 IIf(P.SVC_ID="7000, 7002",0,P.STARTTIME) As Priority,P.CASE_NUM,P.DATE,P.STARTTIME
FROM tblAppts P WHERE P.CASE_NUM=A.CASE_NUM And P.DATE=A.DATE ORDER BY 1]. AS T ON (A.STARTTIME = T.STARTTIME) AND (A.CASE_NUM = T.CASE_NUM);

Since the Subject area is free text, I think it is better to stay with Dr. service codes, which are 7000 and 7002.

I received the following error:
Enter Parameter Value: P.Date
Enter Parameter Value: A.CASE_NUM
Enter Parameter Value: A.DATE

The query produces no results.

 

[lespaul]

what version of Access are you using? Only newer versions (2000 and above I think) will allow you to join into a query like that. You have to create the select top 1 query and save it and then join into the saved query in early versions.

HTH

leslie

 

I am using Access 2003.