convert ms sql query to access sql query

[jcgldr]

hi i'm not so familiar with access syntax i'm working more with ms sql server
i have now a sql server query that i'm trying to use in access but it does not work it seems to be a syntax problem can somone help me transelate this sql server query to access ?

SELECT     g.memberid, g.lastname + ' ' + g.firstname AS name, g.fathername, membertype.typedescription, g.tel, 

(COALESCE (gb.sum_bills, 0) + COALESCE (cb.sum_bills, 0)) - (COALESCE (gp.sumpay, 0) 
                  + COALESCE (cp.sumpay, 0)) AS totaldue, g.groupid 
FROM        members AS g LEFT OUTER JOIN
 membertype ON g.membertype = membertype.typeid LEFT OUTER JOIN
 (SELECT     paymemberid, SUM(payamount) AS sumpay
 FROM        payments
 GROUP BY paymemberid) AS gp ON gp.paymemberid = g.memberid LEFT OUTER JOIN
 (SELECT     m.groupid, SUM(b.payamount) AS sumpay
 FROM        payments AS b INNER JOIN
 members AS m ON m.memberid = b.paymemberid
 WHERE     (m.groupid IS NOT NULL)                     
   GROUP BY m.groupid) AS cp ON cp.groupid = g.memberid LEFT OUTER JOIN
 (SELECT     billmemberid, SUM(billamount) AS sum_bills
 FROM        bill
 GROUP BY billmemberid) AS gb ON gb.billmemberid = g.memberid LEFT OUTER JOIN
 (SELECT     m.groupid, SUM(b.billamount) AS sum_bills
 FROM        bill AS b INNER JOIN
 members AS m ON m.memberid = b.billmemberid
 WHERE     (m.groupid IS NOT NULL)
 GROUP BY m.groupid) AS cb ON cb.groupid = g.memberid

 

[Pat Hartman]

Welcome Aboard

1. Please edit your posting so the code wraps correctly. I can't read it the way it is.
2. Paste your SQL string into the query builder. It will help resolve syntax errors.
3. Although the + can be used for concatenation, the & is the standard concatenation character in Access. Both work for text values but they work differently with nulls. Access help will explain.