I have been given a SQL statement to adapt for use in MS ACCESS 2010. The SQL statement was written in SQL Server so I understand that there will be differences in syntax. I have tried to adapt the query for MS Access but am getting the following error:
Syntax error(missing operator)in query expression ". MS Access is highlighting the AND which is BOLDED in the code. I cannot find the syntax error and need help. Any help would be greatly appreciated.
The code is as follows:
Syntax error(missing operator)in query expression ". MS Access is highlighting the AND which is BOLDED in the code. I cannot find the syntax error and need help. Any help would be greatly appreciated.
The code is as follows:
Code:
SELECT t1.companyid, t1.mASter_clientid, t1.name,t1.addressline1, t1.addressline2,t1.state, t1.zip, t6.corporation, t6.name, t7.salesrep, t1.territory,t2.feeschedule AS Discount_FeeSchedule,t4.FEESCHEDULE AS FlatRate_FeeSchedule, t2.fingroup,t2.effectivedate, t1.perdiem_rate,t4.flatratediscount,t4.fingroup, t4.effectivedate
FROM
(SELECT a1.*
FROM dbo_SLAR_Client AS a1
LEFT JOIN
(SELECT COMPANYID,MASTER_CLIENTID
FROM dbo_SLAR_Client
GROUP BY COMPANYID,MASTER_CLIENTID ) AS a2
ON a1.COMPANYID=a2.COMPANYID and a2.MASTER_CLIENTID=a1.CLIENTID
WHERE a2.MASTER_CLIENTID IS NOT NULL) AS t1
LEFT JOIN
(SELECT y.clientid, y.companyid, y.effectivedate,y.feeschedule, y.fingroup
FROM
(SELECT clientid, companyid, fingroup,max(effectivedate) AS latesteffective
FROM dbo_SLAR_clientdiscountfeeschedule
GROUP BY clientid, companyid, fingroup) AS x
INNER JOIN dbo_SLAR_clientdiscountfeeschedule AS y
ON y.companyid=x.companyid AND y.clientid=x.clientid AND y.fingroup=x.fingroup AND y.effectivedate=x.latesteffective) AS t2
ON t2.companyid=t1.companyid [b]AND[/b] t2.clientid=t1.clientid
LEFT JOIN
(SELECT b.clientid, b.companyid, b.effectivedate, b.fingroup,b.flatratediscount,b.terminationdate,b.FEESCHEDULE
FROM
(SELECT clientid, companyid, fingroup, max(effectivedate) AS latesteffective
FROM dbo_SLAR_clientflatratediscount
GROUP BY clientid, companyid, fingroup) AS a
INNER JOIN dbo_SLAR_clientflatratediscount AS b
ON b.clientid=a.clientid AND b.companyid=a.companyid AND b.fingroup=a.fingroup AND b.effectivedate=a.latesteffective) AS t4
ON t4.companyid=t2.companyid AND t4.clientid=t2.clientid AND t4.fingroup=t2.fingroup
LEFT JOIN
dbo_SLAR_corporation AS t5
ON t5.companyid=t1.companyid AND t5.clientid=t1.clientid
LEFT JOIN dbo_SLAR_corporation_details AS t6
ON t6.companyid=t5.companyid AND t6.corporation=t5.corporation
LEFT JOIN
(SELECT x.companyid, x.clientid, x.MarketingRep
FROM
(SELECT companyid, clientid
FROM client_salesrep
GROUP BY companyid, clientid) AS y
INNER JOIN client_salesrep AS x
ON x.companyid=y.companyid AND x.clientid=y.clientid AS t7
ON t7.companyid=t1.companyid AND t7.clientid=t1.clientid
WHERE t1.inactive_date IS NULL
GROUP BY t1.companyid, t1.master_clientid,t1.name,t1.addressline1,t1.addressline2, t1.state, t1.zip,t6.corporation, t6.name, t7.salesrep, t1.territory,t2.feeschedule,t4.FEESCHEDULE, t2.fingroup, t2.effectivedate, t1.perdiem_rate, t4.flatratediscount, t4.fingroup, t4.effectivedate
ORDER BY t1.master_clientid, t1.companyid, t2.effectivedate;