Combining 3 tables (1 Viewer)

[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

 

[Uncle Gizmo]

Please provide a link to the other posting of the question.

 

[Uncle Gizmo]

>>>tblAppointment.dtmAppointTime AS dtmAppointTime,<<<

I'm not a hundred percent sure but I think you might be accidentally creating a duplicate field here.

Try adding a 1 or 2 or something else to differentiate:-

tblAppointment.dtmAppointTime AS dtmAppointTime2,

 

[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.

 

[Uncle Gizmo]

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

I assumed that you would realise it would need to be done to all of the 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

 

[Uncle Gizmo]

What happens if you paste this back in to the query builder SQL interface?

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 ;

 

[JohnPapa]

Please see attachment for answer

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

 

[Uncle Gizmo]

Have you tried rebuilding the query in the query builder grid?

 

[Uncle Gizmo]

Actually I'm wondering do you really need to do this?

tblAppointment.dtmAppointTime AS dtmAppointTime

Just use:-

tblAppointment.dtmAppointTime

Where you need to. (For them all)

 

[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

 

[Uncle Gizmo]

It would be a good form to mention the problem is solved in the SQL Basics Site, possibly with a link to here.

 

[Uncle Gizmo]

Reviewing the two statements for differences that could cause the reported error I note:-

Not working Version:-
& " LEFT OUTER JOIN tblCustomer ON lngPatientID1 = lngCustomerID1 " _
& " LEFT OUTER JOIN tblDoctor ON lngDoctorID1 = lngAppointDoctorID1 " _

Working Version:-
INNER JOIN tblAppointment ON tblCustomer.lngCustomerID = tblAppointment.lngPatientID) " _
& " ON tblDoctor.lngDoctorID = tblAppointment.lngAppointDoctorID

Note the full statement is used in the working version:-
tblCustomer.lngCustomerID ........ and ....... tblAppointment.lngAppointDoctorID

I wondered as a quick test, if you removed the tblCustomer. and the tblAppointment. from the join to see if you can reproduce the error?

 

[JohnPapa]

Thanks Gizmo,

Will report in other site with link.