Usually this stuff in MS sql and access is left associative, so you have to use parens like this
FROM (((a left join b) left join c) left join d) -----Original Message----- From: Adkins, Randy [mailto:[EMAIL PROTECTED] Sent: Thursday, January 12, 2006 3:14 PM To: CF-Talk Subject: Proper Syntax for Access (CF App) I have created an application using MySQL database platform but a client wants to use the App using MS Access. Now I created the tables and everything but some of the Query statements bomb out in Access. Example 1 (MySQL) SELECT SUBMISSIONRECORDS.EMPLOYEEID, MEMBERS.FIRSTNAME AS EMPLOYEE_FIRST_NAME, MEMBERS.LASTNAME AS EMPLOYEE_LAST_NAME, MANAGER.EMPLOYEEID AS MANAGER_EMPLOYEE_ID, MANAGER.FIRSTNAME AS MANAGER_FIRST_NAME, MANAGER.LASTNAME AS MANAGER_LAST_NAME, RELEASED.EMPLOYEEID AS RELEASED_EMPLOYEE_ID, RELEASED.FIRSTNAME AS RELEASED_FIRST_NAME, RELEASED.LASTNAME AS RELEASED_LAST_NAME, APPROVER.EMPLOYEEID AS APPROVER_EMPLOYEE_ID, APPROVER.FIRSTNAME AS APPROVER_FIRST_NAME, APPROVER.LASTNAME AS APPROVER_LAST_NAME, SUBMISSIONRECORDS.RELEASED, SUBMISSIONRECORDS.APPROVED, PAYPERIODS.ID AS PAYPERIODID, PAYPERIODS.PPSTARTDATE, PAYPERIODS.PPENDDATE, TIMESUBMISSIONS.PPDATE, TIMESUBMISSIONS.ST_TIME, TIMESUBMISSIONS.OT_TIME, TIMESUBMISSIONS.DT_TIME, TIMESUBMISSIONS.JCW_CODE, TIMESUBMISSIONS.JOBCODE, TIMESUBMISSIONS.CRAFTCODE, WAGERATES.WAGERATE FROM SUBMISSIONRECORDS LEFT JOIN MEMBERS ON SUBMISSIONRECORDS.EMPLOYEEID = MEMBERS.ID LEFT JOIN MEMBERS AS MANAGER ON SUBMISSIONRECORDS.MANAGERID = MANAGER.ID LEFT JOIN MEMBERS AS RELEASED ON SUBMISSIONRECORDS.RELEASEDBY = RELEASED.ID LEFT JOIN MEMBERS AS APPROVER ON SUBMISSIONRECORDS.APPROVEDBY = APPROVER.ID LEFT JOIN PAYPERIODS ON SUBMISSIONRECORDS.PAYPERIODID = PAYPERIODS.ID LEFT JOIN TIMESUBMISSIONS ON SUBMISSIONRECORDS.ID = TIMESUBMISSIONS.SUBRECID LEFT JOIN WAGERATES ON TIMESUBMISSIONS.WAGERATE = WAGERATES.ID This worked perfectly in MySQL but then requires the ( ) in the FROM line on Access such as: (SUBMISSIONRECORDS LEFT JOIN MEMBERS ON SUBMISSIONRECORDS.EMPLOYEEID = MEMBERS.ID) Now if I only had ONE join transaction, it would be okay, what is the syntax for multiple Joins? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:229379 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
