tricky sql select

Im having a problem with an sql select query which i use to put into a datagrid:
I have two tables:
Patient_data
Patient_details

The Patient_data table is made up of:
term
code
P_id (Unique to each patient)

The patient details is made up of :
Name
surname
P_id(Unique to each patient)

Sample data:
-------------------------
Patient_details
Forename - John
Surname - jones
P_id - 24

Patient_data
p_id - 24
term - broken toe
term - back ache
term - thigh strain
--------------------------

What i need to do is select the patient name and list their terms in one field, in the query.What i have so far:

sql="SELECT DISTINCT [FORENAME_1] & " " & [Surname] AS [Patient Name], PATIENT_data.TERM
FROM PATIENT_DETAILS, PATIENT_data;"

What i want to eventually do is list every patient,have their term in the field based on what code the term represents.
help much appriciated...thanks dave

Im having a problem with an sql select query which i use to put into a datagrid:
I have two tables:
Patient_data
Patient_details

The Patient_data table is made up of:
term
code
P_id (Unique to each patient)

The patient details is made up of :
Name
surname
P_id(Unique to each patient)

Sample data:
-------------------------
Patient_details
Forename - John
Surname - jones
P_id - 24

Patient_data
p_id - 24
term - broken toe
term - back ache
term - thigh strain
--------------------------

What i need to do is select the patient name and list their terms in one field, in the query.What i have so far:

sql="SELECT DISTINCT [FORENAME_1] & " " & [Surname] AS [Patient Name], PATIENT_data.TERM
FROM PATIENT_DETAILS, PATIENT_data;"

What i want to eventually do is list every patient,have their term in the field based on what code the term represents.
help much appriciated...thanks daveWhat you need is:

sql="SELECT DISTINCT a.FORENAME_1 & " " & a.Surname AS Patient Name, b.TERM
FROM PATIENT_DETAILS a, PATIENT_data b Where a.P_id = b.p_id"


You will still have multiple results if you have multiple terms...

thanks for the reply...
but this still doesnt list the terms inside one field, it still has multiple names for each patient..

Try using a GROUP BY. Group By each patient name

wont let me group by patient name-- syntax error.
im also trying this in access

When you say youre ding this in access as well, Im assumimg you are doing using a sql query window, why dont you make the query in its standerd/defualt format, and then view the sql equivalent?

Right click the blank grey part at query desin time, and click sql view

Any particular reason why you need a single field with all of the terms in that one field? It seems counter-intuitive to handling query results to bunch up and restrict everything into one field.

I need to list the terms associated with the patient, which has been recorded in the db.
I am trying the sql in access in the sql editor then taking it across in to my vb code, makes life easier.:)

Well, you probably want to create a Cross Tab Query. See, a "normal" SELECT will give you several records for each patient. Each record will have a unique term for that patient. The cross tab query will "create" fields for each term in a single row for a patient. Look in the help for the particulars to set up the query.

The result will be a term field for the maximum number of terms per patient. For example, if the maximum terms per patient so far is 6, there will be one record for the patient with fields Term1 to Term6. Any patient without 6 terms will have NULLs in the fields.

This is not really the best way to handle what you want to do though. I would instead work with the "normal" SELECT's recordset and use code logic to determine a change in the patient. Loop through the recordset this way to get a patient's term info and manipulate it the way you need.