Extracting data from 2 tables

[eussias]

Can anyone help? I have two tables (Blood and Medical). Both have a common field (ClientNum). There can only be one occurence of ClientNum in Medical (Primary Key) but numerous in Blood. The Blood tables contains records for different Weeks for patients (WeekNum being another field). WeekNum can be 0,12,26,52 or 78. Not all patients have the same number of assessments carried out (i.e some may only have 0 done, others 0,12 and 26 etc) What I'm hoping to do is write an SQL query that will allow me to extract the maximum WeekNum for each patient when a corresponding Yes/No field (TMentStop) is true. I would appreciate any input. Thanks

 

[crose7227]

select patientID, max(weeknum) from tblBlood land of milk and Honey

 

[crose7227]

oops didn't see the last part

if the yes/no is in the other table then do this


select patientID, max(weeknum) from tblBlood,tblMed
where TMentStop.tblMed = yes
land of milk and Honey

 

[crose7227]

hmm for each patient... should have read ur post more carefully...

lets see... land of milk and Honey

 

[eussias]

Crose7227

Think I figured it out. I just ran a query in the Blood table to get the max(WeekNum) for each Patient, then ran another query on that query with the second criteria from the Medical Table. Seemed to work fine. Thanks