Combining 3 tables

[JohnPapa]

I have 3 tables, tblAppointment, tblCustomer, tblDoctor.
tblCustomer has a one-to-many relationship with tblAppointment
tblDoctor has a one-to-many relationship with tblAppointment

The following works and gets the appointment and customer info

rst.Open "SELECT tblAppointment.lngPatientID AS lngPatientID, tblCustomer.lngCustomerID AS lngCustomerID, " _
       & " tblAppointment.lngAppointDoctorID AS lngAppointDoctorID, tblAppointment.dtmAppointDate AS dtmAppointDate, " _
       & " tblAppointment.dtmAppointTime AS dtmAppointTime, tblAppointment.txtAppointDesc AS txtAppointDesc, " _
       & " tblCustomer.txtSurname + ' ' + tblCustomer.txtName + ' ' + tblCustomer.txtTelephone1 AS nvcqCustomerName " _
       & " FROM tblAppointment " _
       & " LEFT OUTER JOIN tblCustomer ON tblAppointment.lngPatientID = tblCustomer.lngCustomerID " _
       & " WHERE (tblAppointment.lngAppointDoctorID = 1) AND (dtmAppointDate = #07/27/2015#) ORDER BY dtmAppointTime ASC ;", CurrentProject.Connection, adOpenKeyset, adLockPessimistic

I tried to include the Doctor info with something like

rst.Open "SELECT tblAppointment.lngPatientID AS lngPatientID, tblCustomer.lngCustomerID AS lngCustomerID, " _
       & " tblAppointment.lngAppointDoctorID AS lngAppointDoctorID, tblAppointment.dtmAppointDate AS dtmAppointDate, " _
       & " tblAppointment.dtmAppointTime AS dtmAppointTime, tblAppointment.txtAppointDesc AS txtAppointDesc, " _
       & " tblDoctor.lngDoctorID AS lngDoctorID, tblDoctor.txtDoctorSurname AS txtDoctorSurname " _
       & " tblCustomer.txtSurname + ' ' + tblCustomer.txtName + ' ' + tblCustomer.txtTelephone1 AS nvcqCustomerName " _
       & " FROM tblAppointment " _
       & " LEFT OUTER JOIN tblCustomer ON tblAppointment.lngPatientID = tblCustomer.lngCustomerID " _
       & " LEFT OUTER JOIN tblDoctor ON tblDoctor.lngDoctorID = tblAppointment.lngAppointDoctorID " _
       & " WHERE (tblAppointment.lngAppointDoctorID = 1) AND (dtmAppointDate = #07/27/2015#) ORDER BY dtmAppointTime ASC ;", CurrentProject.Connection, adOpenKeyset, adLockPessimistic

I get "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect"

I tried various permutations without success. Any ideas very welcome.

 

[JohnPapa]

I also tried

'rst.Open " SELECT tblDoctor.lngDoctorID AS lngDoctorID, tblDoctor.txtDoctorSurname AS txtDoctorSurname " _
       & " FROM tblDoctor " _
       & " RIGHT OUTER JOIN " _
       & "   (SELECT tblAppointment.lngPatientID AS lngPatientID, tblCustomer.lngCustomerID AS lngCustomerID, " _
       & "    tblAppointment.lngAppointDoctorID AS lngAppointDoctorID, tblAppointment.dtmAppointDate AS dtmAppointDate, " _
       & "    tblAppointment.dtmAppointTime AS dtmAppointTime, tblAppointment.txtAppointDesc AS txtAppointDesc, " _
       & "    tblCustomer.txtSurname + ' ' + tblCustomer.txtName + ' ' + tblCustomer.txtTelephone1 AS nvcqCustomerName " _
       & "    FROM tblAppointment " _
       & "    LEFT OUTER JOIN tblCustomer ON tblAppointment.lngPatientID = tblCustomer.lngCustomerID  " _
       & "    WHERE (tblAppointment.lngAppointDoctorID = 1) AND (dtmAppointDate = #07/27/2015#) ORDER BY dtmAppointTime ASC) " _
       & " ON tblDoctor.lngDoctorID = tblAppointment.lngAppointDoctorID " _
       & " ;", CurrentProject.Connection, adOpenKeyset, adLockPessimistic

 

[JohnPapa]

Just to let you know that since this may not be the appropriate site for this query, I submitted same question to tutorialized.com

 

[JohnPapa]

Link is:-

http://forums.tutorialized.com/sql-basics-113/combining-3-tables-397674.html

On second look I do not think it will be a responsive attempt

 

[JohnPapa]

I tried renaming the variable. It did not help. Why did you single out the "dtmAppointTime" variable and not any of the other variables.

 

[JohnPapa]

I changed all, no luck

rst.Open "SELECT tblAppointment.lngPatientID AS lngPatientID1, tblCustomer.lngCustomerID AS lngCustomerID1, " _
       & " tblAppointment.lngAppointDoctorID AS lngAppointDoctorID1, tblAppointment.dtmAppointDate AS dtmAppointDate1, " _
       & " tblAppointment.dtmAppointTime AS dtmAppointTime1, tblAppointment.txtAppointDesc AS txtAppointDesc1, " _
       & " tblDoctor.lngDoctorID AS lngDoctorID1, tblDoctor.txtDoctorSurname AS txtDoctorSurname1 " _
       & " tblCustomer.txtSurname + ' ' + tblCustomer.txtName + ' ' + tblCustomer.txtTelephone1 AS nvcqCustomerName1 " _
       & " FROM tblAppointment " _
       & " LEFT OUTER JOIN tblCustomer ON lngPatientID1 = lngCustomerID1 " _
       & " LEFT OUTER JOIN tblDoctor ON lngDoctorID1 = lngAppointDoctorID1 " _
       & " WHERE (lngAppointDoctorID1 = 1) AND (dtmAppointDate1 = #07/27/2015#) ORDER BY dtmAppointTime1 ASC ;", CurrentProject.Connection, adOpenKeyset, adLockPessimistic

 

[Gasman]

Shouldn't you be using '&' and not '+' ?

I would put all that into a string and rst.Open (string) and then look at in in debug?

Edit: I see you said that + syntax works. I'd still try the string method to view the string

 

[JohnPapa]

Please see attachment for answer

I also tried using & instead of + (SQL Server). Both seem to work. Thanks for pointing out.

 

[JohnPapa]

Tried a stripped down version of the query in the query builder grid and the following works

SELECT tblDoctor.txtDoctorSurname, tblAppointment.dtmAppointTime, tblCustomer.txtSurname, tblCustomer.txtName
FROM tblDoctor INNER JOIN (tblCustomer INNER JOIN tblAppointment ON tblCustomer.lngCustomerID = tblAppointment.lngPatientID) ON tblDoctor.lngDoctorID = tblAppointment.lngAppointDoctorID;

I should be able to build the full query.

Thanks Uncle Gizmo

 

[JohnPapa]

The following works and actually it is the Inner Join which I wanted all along, since I want only the occupied appointments

rst.Open "SELECT tblAppointment.lngPatientID AS lngPatientID, tblCustomer.lngCustomerID AS lngCustomerID, " _
       & " tblAppointment.lngAppointDoctorID AS lngAppointDoctorID, tblAppointment.dtmAppointDate AS dtmAppointDate, " _
       & " tblAppointment.dtmAppointTime AS dtmAppointTime, tblAppointment.txtAppointDesc AS txtAppointDesc, " _
       & " tblDoctor.txtDoctorSurname, " _
       & " tblCustomer.txtSurname & ' ' & tblCustomer.txtName & ' ' & tblCustomer.txtTelephone1 AS nvcqCustomerName " _
       & " FROM tblDoctor INNER JOIN (tblCustomer INNER JOIN tblAppointment ON tblCustomer.lngCustomerID = tblAppointment.lngPatientID) " _
       & " ON tblDoctor.lngDoctorID = tblAppointment.lngAppointDoctorID " _
       & " WHERE (tblAppointment.lngAppointDoctorID = " & lngDoctorIDInSN & ") AND (dtmAppointDate = #07/27/2015#) ORDER BY dtmAppointTime ASC ;", CurrentProject.Connection, adOpenKeyset, adLockPessimistic

 

[JohnPapa]

Thanks Gizmo,

Will report in other site with link.