SQL Query - Ok its from a windows app

I think I need a nested query but here's the problem. I promise i've
exerted my meager brain power on this problem & ask this as a last
resot!

3 Tables
PatientTable
PatActive, PatID
ProfileTable
PatID, CodeID, OtherInfo, MedicineID
MedicineTable
MedicineID MedicineInfo



I need a return from Table2 Info including the detailed child data from
Table3 [OtherInfo] where patient is active, however i only want rows
from Profile table where the element CodeID is MAX for each patient.

subquery is fine
SELECT MAX(ProfileTab.CodeID) AS CodeID , patienttable.patsurname FROM
(profileTable INNER JOIN patienttable ON profiletable.PatID=
patienttable.PatID) GROUP BY patienttable.patsurname HAVING
(patienttable.patactive = 1)

I had to add the Group by statement when I tried to add this into
another query.

I dont have direct access to the DB so cannot use stored queries.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

mithril
Fri Feb 18 21:19:51 CST 2005


I have a query that gets the desired results in ACCESS but now the
DataAdapter doesn't like it.

Internally it runs (from the wizard) but I cannot make a dataset from
it!?

man this is frustrating.

BTW the SQL I came up with is as follows
(note i discover I don't need table1)

SELECT Profiletable.propnameid, Profiletable.CodeID,
Profiletable.patientid, MedicineTable.MedicineInfo
FROM MedicineTableRIGHT JOIN (Profiletable INNER JOIN [SELECT
Profiletable.patientid, Max(Profiletable.CodeID) AS MaxOfCodeID
FROM Profiletable GROUP BY Profiletable.patientid]. AS E1 ON
Profiletable.CodeID = E1.MaxOfCodeID) ON MedicineTable.MedicineID =
Profiletable.MedicineID;

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Ron
Sat Feb 19 11:39:29 CST 2005

mithril,
In general the wizards don't handle joined results correctly as they
can't figure out how to write the Update/Insert/Delete commands from the
SELECT. You can manually create a DataAdapter and run code to load the
resulting table into a DataSet though.

Ron Allen
"mithril" <nospam@hotmail> wrote in message
news:e3LtgHjFFHA.2676@TK2MSFTNGP12.phx.gbl...
>
> I have a query that gets the desired results in ACCESS but now the
> DataAdapter doesn't like it.
>
> Internally it runs (from the wizard) but I cannot make a dataset from
> it!?
>
> man this is frustrating.
>
> BTW the SQL I came up with is as follows
> (note i discover I don't need table1)
>
> SELECT Profiletable.propnameid, Profiletable.CodeID,
> Profiletable.patientid, MedicineTable.MedicineInfo
> FROM MedicineTableRIGHT JOIN (Profiletable INNER JOIN [SELECT
> Profiletable.patientid, Max(Profiletable.CodeID) AS MaxOfCodeID
> FROM Profiletable GROUP BY Profiletable.patientid]. AS E1 ON
> Profiletable.CodeID = E1.MaxOfCodeID) ON MedicineTable.MedicineID =
> Profiletable.MedicineID;
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!