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

[Robeen]

I'm not sure if there are other syntax issues [I can't see any], but I've never seen 'COALESCE' used in Access SQL.
Can you run the query without that and see if it works?

What is 'COALESCE' meant to do? There may be a way of doing that in Access - but no guarantees . . .

[jcgldr]

thanks for your response
the coalesce function converts null value to 0 it makes sense that it is only a sql function
anyway i tried with out this and it still didn't work

SELECT     g.memberid, g.lastname + ' ' + g.firstname AS name, g.fathername, membertype.typedescription, g.tel,
 ( gb.sum_bills + cb.sum_bills) - (gp.sumpay + cp.sumpay) 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

[Robeen]

Access has a SQL function to convert nulls to some other value:
Select Nz(FieldName,0) From . . .
Let me know if that helps!

Your SQL is a little more complex than what I usually use, but see if this Microsoft page is any help with subqueries in Access.
You might see something there that sheds some light on why you are getting an error.

Please let me know what you find out. I'm interested to know.

Also, members ruralguy, pbaldy and June7 might be able to help. If you need further help, you can send them private messages and ask them for help by posting the url this thread.
Just look through previous posts and find those names and click their names . . .

Sorry I couldn't be of more help!

[recyan]

Currently not focusing on the syntax.
Are the below 2 correct in your query functionally ?
1)

ON 
	cp.groupid = g.memberid

2)

	ON cb.groupid = g.memberid

Thanks

[jcgldr]

yes it is fuctioning

[June7]

Robeen, I don't think the link got into your post.

Yes, Nz is Access/VBA equivalent for COALESCE.

I can't see anything wrong with the query.

Did the Nz substitution resolve the issue?

[jcgldr]

i tried nz and it didn't work as far as i understand looks like the problem is something with the joins that they are not nested in the proper way but i can not figure out what is the problem

[recyan]

See if below gives some guidelines :
The sub-queries :
1)
gp

    
SELECT 
    paymemberid, 
    SUM(payamount) AS TheGPsumpay
FROM 
    payments
GROUP BY 
    paymemberid;

2)
cp

    
SELECT 
    m.groupid, 
    Sum(b.payamount) AS TheCPsumpay
FROM 
    payments AS b 
    INNER JOIN 
    members AS m 
    ON 
        b.paymemberid = m.memberid
WHERE 
    (((m.groupid) Is Not Null))
GROUP BY 
    m.groupid;

3)
gb

    
SELECT 
    billmemberid, 
    SUM(billamount) AS TheGBsum_bills
FROM 
    bill
GROUP BY 
    billmemberid;

4)
cb

    
SELECT 
    m.groupid, 
    Sum(b.billamount) AS TheCBsum_bills
FROM 
    bill AS b 
    INNER JOIN 
    members AS m 
    ON b.billmemberid = m.memberid
WHERE 
    (((m.groupid) Is Not Null))
GROUP BY 
    m.groupid;

__________________________________________________ _______________________


The final query :

                       
SELECT 
    members.memberID, 
    members.lastName, 
    members.firstName, 
    [lastName] & " " & [firstName] AS TheName, 
    members.fatherName, 
    memberType.typeDescription, 
    members.tel, 
    members.groupID, 
    Nz([TheGPsumpay],0) AS TheGPSumPays, 
    Nz([TheCPsumpay],0) AS TheCPSumPays, 
    Nz([TheGBsum_bills],0) AS TheGBSumBills, 
    Nz([TheCBsum_bills],0) AS TheCBSumBills, 
    ([TheGBSumBills]+[TheCBSumBills])-([TheGPSumPays]+[TheCPSumPays]) AS TotalDues
FROM 
    (
        (
            (
                (
                    members 
                    LEFT JOIN 
                    memberType 
                    ON 
                    members.memberType = memberType.typeID
                ) 
                LEFT JOIN 
                gp 
                ON 
                members.memberID = gp.paymemberid
            ) 
            LEFT JOIN 
            cp 
            ON 
            members.memberID = cp.groupid
        ) 
        LEFT JOIN 
        gb 
        ON 
        members.memberID = gb.billmemberid
    ) 
    LEFT JOIN 
    cb 
    ON 
    members.memberID = cb.groupid;

Thanks

[jcgldr]

thank you very much
i actualy figured it out yesterday like you said i first created the 4 queries and then the final
but i would like to have it all in one query like the above sql serer query is it possible?

[June7]

Open each query in SQL View. Copy/Paste the SQL string of each subquery into the final query in appropriate location with parens and the AS keyword.

[jcgldr]

thank to you all

SELECT     members.memberid, members.lastname + ' ' + members.firstname AS name, members.fathername, membertype.typedescription, members.tel, iif(isnull(cb.sum_bills), 0, cb.sum_bills) + iif(isnull(gb.sum_bills), 0, 
                  gb.sum_bills) + iif(isnull(cp.sumpay), 0, cp.sumpay) - iif(isnull(gp.SumOfpayamount), 0, gp.SumOfpayamount) AS total
FROM        (membertype INNER JOIN
                  ((((members LEFT OUTER JOIN 
(SELECT members.memberid, Sum(payments.payamount) AS SumOfpayamount
FROM members INNER JOIN payments ON members.memberid=payments.paymemberid
GROUP BY members.memberid) as
                  gp ON members.memberid = gp.memberid) LEFT OUTER JOIN
(SELECT bill.billmemberid, Sum(bill.billamount) AS sum_bills
FROM bill
GROUP BY bill.billmemberid) as
                  gb ON members.memberid = gb.billmemberid) LEFT OUTER JOIN

(SELECT m.groupid, Sum(b.payamount) AS sumpay
FROM members AS m INNER JOIN payments AS b ON m.memberid=b.paymemberid
WHERE (((m.groupid) Is Not Null))
GROUP BY m.groupid)

                  cp ON members.memberid = cp.groupid) LEFT OUTER JOIN

(SELECT m.groupid, Sum(b.billamount) AS sum_bills

FROM members AS m INNER JOIN bill AS b ON m.memberid=b.billmemberid
WHERE (((m.groupid) Is Not Null))
GROUP BY m.groupid)

                  cb ON members.memberid = cb.groupid) ON membertype.typeid = members.membertype)