Select Problem 2

[bmcguirk]

I have two tables that simplified would look like this

tblPatients
PatientNumber
PatientName

tblVisits
VisitNumber
PatientNumber
VisitDate


I need to do a select that will take the PatientNumber and Name from Patients if they haven't visited since 12/31/98. For example, Joe has two visits in the visit table, one on 2/10/98 and the other on 1/26/99. He should not be selected. Steve also has two visits in the table. One on 7/5/98 and one on 7/7/98. He should be selected.

Can anyone lead me to the right SQL statement? I keep getting returns with both Joe and Steve on the list.

Thanks!

 

[karluk]

I would expect something like the following to work. Is it similar to what you are trying?

select patientnumber, patientname from tblpatients a
where not exists
(select b.patientnumber from tblvisits b
where a.patientnumber=b.patientnumber
and b.visitdate > '31-dec-1998')

 

[Cagliostro]

Better will be
select * from tblPatients
where PatientNumber not in(select PatientNumber from tblVisits where visitdate > '12/31/98')
John Fill

 

[bmcguirk]

Thanks for both responses, but they both yielded the same result which was to not return any rows. I feel like both of these statements were very close, but one condition is missing. Any other thoughts?

 

[karluk]

Sorry to disagree, but I just tested my select, so I know it works on your test data. John Fill's solution also looks good.

Since you are having problems, I would speculate that it's your data that's causing the apparent difficulty. One obvious issue is the turn of century. When you enter a two digit year like '98', it's unclear whether your DBMS interprets it as 1998 or 2098. Please check to see if this is the problem.

 

[bmcguirk]

To be more specific I think I need a statement that performs three steps:

1. Select the PatientNumber and the most recent VisitDate for that PatientNumber from Visits.

2. Select PatientNumber from step 1's results where the most recent VisitDate before 12/31/98

3. Get the PatientName from tblPatients based on step 2's results.

Does this make better sense?

 

[karluk]

Here is some SQL that implements your logic:

select patientname from tblpatients
where patientnumber in
(select patientnumber from tblvisits
group by patientnumber
having max(visitdate) <= '31-dec-1998')

Based on what I'm seeing, all three suggested solutions are equivalent. I execute each statement using your sample data, and every one selects Steve and leaves out Joe. I'm afraid if there is really a problem with your data, just modifying the SQL won't fix things.

 

[bmcguirk]

karluk,

I agree that both solutions look good, I can't for the life of me find my mistake. I'll paste my data and the query that is returning no results.

First the data:

Patients

PatientID LName
5 Joe
6 Steve

Visits

VisitID PatientID VisitDate
7 5 2/10/1998
8 5 1/26/1999
9 6 7/5/1998
10 6 7/7/1998

Now the query:

SELECT PatientID, LName
FROM Patients a
WHERE not exists

(select b.PatientID from Visits b
where a.PatientID=b.PatientID
and b.VisitDate > 12/31/1998);

This is in Access '97. Thanks for taking a look at all of this, you're all helping to save programming time.

 

[karluk]

The only thing that I can think of is that maybe VisitDate isn't defined as a date/time column. If you are doing string compares, 7/7/1998 would be greater than 12/31/1998, even though it is smaller as a date.

 

[bmcguirk]

karluk,

Don't know if you're still reading this thread, but all of the SQL that you threw at me works. I just got the thing up and running today. Access just needed some #'s around the date I was giving it so see it as a date instead of some long division. Thanks for all of your help!

--B