Combining 3 tables

JohnPapa · Jul 26, 2015

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

Code:

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

Code:

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 · Jul 26, 2015

I also tried

Code:

'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 · Jul 26, 2015

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 · Jul 26, 2015

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

Uncle Gizmo · Jul 26, 2015

>>>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 · Jul 26, 2015

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 · Jul 26, 2015

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 · Jul 26, 2015

JohnPapa said:
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 · Jul 26, 2015

I changed all, no luck

Code:

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 · Jul 26, 2015

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 · Jul 26, 2015

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 · Jul 26, 2015

Please see attachment for answer

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

Uncle Gizmo · Jul 26, 2015

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

Uncle Gizmo · Jul 26, 2015

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 · Jul 26, 2015

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

Code:

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 · Jul 26, 2015

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

Code:

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 · Jul 26, 2015

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 · Jul 26, 2015

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 · Jul 27, 2015

Thanks Gizmo,

Will report in other site with link.

Combining 3 tables

Registered User.

Registered User.

Registered User.

Nifty Access Guy

Nifty Access Guy

Registered User.

Registered User.

Nifty Access Guy

Registered User.

Enthusiastic Amateur

Nifty Access Guy

Registered User.

Attachments

Nifty Access Guy

Nifty Access Guy

Registered User.

Registered User.

Nifty Access Guy

Nifty Access Guy

Registered User.

Similar threads

Users who are viewing this thread