Sum of row values

[beulajo]

Hi,

I have two tables in MSACCESS.
1. Sales
2. Receipts

Whatever be the sale I will raise invoice which consists of unique invoice no.

after raising invoice I may receive the payment either full cash or in installment basis.

in this case the scenario is In SALES table - invoice number will be unique. whereas in RECEIPT table invoice number will be repeated which has invoice id as reference from sales table

Now problem starts here. I need to generate a report of invoice amount, received amount and yet to receive amount

I have 208 entries in sales table and 117 entries in receipts table

now my query need to generate only 208 rows which must have all the values of sales table and matching invoiceid of receipt table. but in receipt table invoice id will be multiple I need to sum up all the values of same invoiceid and bring it as one record so that I get only 208 records as a report

This is the query I have written: This must return only 208 rec instead it returns 212 rec because it is not summing up the receivedamt which consists of same invoiceno..

SELECT C.CLIENTCODE, S.COMPANY, D.DEPTCODE, T.TEAM, P.PROJECTCODE, S.INVOICEDATE, S.INVOICENO, S.INVOICEPARTICULARS, S.INVOICEAMT, R.RECEIVEDAMT,
(SUM(S.INVOICEAMT) - SUM(R.RECEIVEDAMT)) AS [YET TO RECEIVE]
FROM ((((SALES AS S LEFT JOIN RECEIPTS AS R ON S.INVOICEID = R.INVOICEID) INNER JOIN PROJECTS AS P ON S.ProjectID = P.ProjectID) INNER JOIN CLIENTELE AS C ON P.ClientID = C.ClientID) INNER JOIN TEAM AS T ON P.TeamID = T.TeamID) INNER JOIN DEPARTMENT AS D ON T.DeptID = D.DeptID
GROUP BY C.CLIENTCODE, S.COMPANY, D.DEPTCODE, T.TEAM, P.PROJECTCODE, S.INVOICEDATE, S.INVOICENO, S.INVOICEPARTICULARS, S.INVOICEAMT, R.RECEIVEDAMT;

How will i achieve this report using a single query...

Thanks in advance

[NeoPa]

Your question doesn't match the SQL you posted. No wonder you have had no response yet. How do the [Projects], [Clientele], [Team] and [Department] tables fit into the requirement?

[NeoPa]

The answer to your actual question, would be :

SELECT   S.COMPANY
       , S.INVOICEDATE
       , S.INVOICENO
       , S.INVOICEPARTICULARS
       , S.INVOICEAMT
       , Sum(R.RECEIVEDAMT) AS Received
       , (S.INVOICEAMT - [Received]) AS [YET TO RECEIVE]
 
FROM     SALES AS S
         LEFT JOIN
         RECEIPTS AS R
  ON     S.INVOICEID = R.INVOICEID
 
GROUP BY S.COMPANY
       , S.INVOICEDATE
       , S.INVOICENO
       , S.INVOICEPARTICULARS
       , S.INVOICEAMT;

When we see what your question should have been we can hopefully move on, but putting it basically, you could use this SQL within a sub-query (See Subqueries in SQL), to handle mixing this with other tables.