Sql error

[topone]

Hello,
I am trying to write an sql statement but an error keeps coming up.
I need to retrieve data from more than one table so I a using the INNer JOIN statement, but still no luck.
The error is in the from part according to the error message.
Any help welcome
The code part is

SQLStr = SQLStr & "DateDiff('yyyy', Patient.dob,Consultation.ConsultDate) AS PatientAge "
SQLStr = SQLStr & "FROM (Consultation INNER JOIN Surgery ON Consultation.SurgeryID = Surgery.SurgeryID) "
SQLStr = SQLStr & "INNER JOIN Patient ON Consultation.PatientID = Patient.PatId "
SQLStr = SQLStr & "FROM (Consultation INNER JOIN Patient ON Consultation.PatientID = Patient.PatId) "
SQLStr = SQLStr & "(INNER JOIN Surgery ON Patient.SurgeryID = Surgery.SurgeryID) AND "
SQLStr = SQLStr & " (Clinicians ON Consultation.ClinicianID=Clinicians.ClinicianID) "
SQLStr = SQLStr & " WHERE ((Consultation.BatchNum) = " & intCurrentBatch & ") "
SQLStr = SQLStr & "ORDER BY Consultation.ConsultId;"

and Part of the debug print is

SELECT Consultation.ConsultId,Consultation.ConsultDate , Consultation.HowLongEnterData,Clincians.name, Clinicians.RPSGB, Surgery.SurgeryName, Patient.PostCode1, MID$(Patient.PostCode2,1,1) AS PostCode2, Patient.Ethnicity_ID, Patient.Ethnicity_Other_text, DateDiff('yyyy', Patient.dob,Consultation.ConsultDate) AS PatientAge FROM (Consultation INNER JOIN Patient ON Consultation.PatientID = Patient.PatId) (INNER JOIN Surgery ON Patient.SurgeryID = Surgery.SurgeryID) AND (Clinicians ON Consultation.ClinicianID=Clinicians.Clinician
ID) WHERE ((Consultation.BatchNum) = 2) ORDER BY Consultation.ConsultId;

 

[johnwm]

Hi topone and welcome to Tek-Tips. To get the best from the forum, read faq222-2244.

For this problem, I notice a couple of things.

In the first line of the debug:
Clincians.name
you appear to be missing the second 'i' in Clinicians

I also don't understand the 'AND' construct in line 4 of the debug. I would be expecting another inner join.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[topone]

Hello Johnwm,
I did remove the and but still does not work.
I also tried to check the FAQ that you suggested but it cames up with an error page saying that is no longer there. Any other suggestions?
Thanks
Paolo

 

[johnwm]

The faq appears to be OK. It's still worth reading. If you have made the 2 amendments to the debug and it still isn't working we need some more info. Maybe a new debug (make sure you copy&paste) and the error msg.

You may need to show the code that you call the SQL with as well

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[topone]

This is the debug.print and the error is "Syntax error in FROM clause."
Let me know waht you think?
I was checking if the string does have a maximum number of characters, but even in that case the debug.print should show if any characters are missing.
Thanks
Paolo


SELECT Consultation.ConsultId,Consultation.ConsultDate ,
Consultation.ConsultTime , Clinicians.Name, Clinicians.RPSGB, Consultation.PatientID, Consultation.BatchNum, Consultation.MedicineDispensed, Consultation.NotPharmacy, Consultation.NotPharmacyOther, Consultation.AnotherHealthcare, Consultation.HealthcareProvider, Consultation.HealthcareProviderOther, Consultation.PatientPresent,
Consultation.PatientNotPresentReason, Consultation.VoucherNumber, Consultation.VoucherDate, Consultation.WhyNotDispensed, Consultation.WhyNotDispensedOther, Consultation.WhyNotDispensedCondition, Consultation.HowLongEnterData,Surgery.SurgeryName, Patient.PostCode1, MID$(Patient.PostCode2,1,1) AS PostCode2, Patient.Ethnicity_ID, Patient.Ethnicity_Other_text,
DateDiff('yyyy', Patient.dob,Consultation.ConsultDate) AS PatientAge (INNER JOIN Clinicians ON Consultation.ClinicianID = Clinicians.ClinicianID)
FROM (Consultation INNER JOIN Patient ON Consultation.PatientID = Patient.PatId)
(INNER JOIN Surgery ON Patient.SurgeryID = Surgery.SurgeryID)
(INNER JOIN Clinicians ON Consultation.ClinicianID = Clinicians.ClinicianID)
WHERE ((Consultation.BatchNum) = 3) ORDER BY Consultation.ConsultId

 

[MarkSweetland]

You have an extra

(INNER JOIN Clinicians ON Consultation.ClinicianID = Clinicians.ClinicianID)

just before the FROM clause...


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[topone]

Hello,
my mistake in copying the wrong version.There are no two equals INNER JOIN.

The fields in the table Clinicians are
RPSGB,Name and ClinicianID if is of any use.
Thanks
Paolo



SELECT Consultation.ConsultId,Consultation.ConsultDate ,
Consultation.ConsultTime , Clinicians.Name, Clinicians.RPSGB, Consultation.PatientID, Consultation.BatchNum, Consultation.MedicineDispensed, Consultation.NotPharmacy, Consultation.NotPharmacyOther, Consultation.AnotherHealthcare, Consultation.HealthcareProvider, Consultation.HealthcareProviderOther, Consultation.PatientPresent,
Consultation.PatientNotPresentReason, Consultation.VoucherNumber, Consultation.VoucherDate, Consultation.WhyNotDispensed, Consultation.WhyNotDispensedOther, Consultation.WhyNotDispensedCondition, Consultation.HowLongEnterData,Surgery.SurgeryName, Patient.PostCode1, MID$(Patient.PostCode2,1,1) AS PostCode2, Patient.Ethnicity_ID, Patient.Ethnicity_Other_text,
DateDiff('yyyy', Patient.dob,Consultation.ConsultDate) AS PatientAge
FROM (Consultation INNER JOIN Patient ON Consultation.PatientID = Patient.PatId)
(INNER JOIN Surgery ON Patient.SurgeryID = Surgery.SurgeryID)
(INNER JOIN Clinicians ON Consultation.ClinicianID = Clinicians.ClinicianID)
WHERE ((Consultation.BatchNum) = 3) ORDER BY Consultation.ConsultId

 

[MarkSweetland]

Access has a peculiar habit of requiring parentheses around multiple inner joins. Try something like this:



SELECT Consultation.ConsultId,Consultation.ConsultDate ,
Consultation.ConsultTime , Clinicians.Name, Clinicians.RPSGB, Consultation.PatientID, Consultation.BatchNum, Consultation.MedicineDispensed, Consultation.NotPharmacy, Consultation.NotPharmacyOther, Consultation.AnotherHealthcare, Consultation.HealthcareProvider, Consultation.HealthcareProviderOther, Consultation.PatientPresent,
Consultation.PatientNotPresentReason, Consultation.VoucherNumber, Consultation.VoucherDate, Consultation.WhyNotDispensed, Consultation.WhyNotDispensedOther, Consultation.WhyNotDispensedCondition, Consultation.HowLongEnterData,Surgery.SurgeryName, Patient.PostCode1, MID$(Patient.PostCode2,1,1) AS PostCode2, Patient.Ethnicity_ID, Patient.Ethnicity_Other_text,
DateDiff('yyyy', Patient.dob,Consultation.ConsultDate) AS PatientAge


FROM
(
(
Consultation INNER JOIN Patient ON Consultation.PatientID = Patient.PatId
) INNER JOIN Surgery ON Patient.SurgeryID = Surgery.SurgeryID
) INNER JOIN Clinicians ON Consultation.ClinicianID = Clinicians.ClinicianID
WHERE Consultation.BatchNum = 3
ORDER BY Consultation.ConsultId;



Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach