Query not showing cost correctly

[munkee]

All,

I have a query which picks out the final date of the last email sent within my system. The query is as follows:

SELECT tbllog.NCC_ID, tbllog.DteReport, tbllog.DteOccur, tbldept_1.Department AS DeptRaisedBy, tbldept.Department AS DeptResp, tblstatus.NCStatus, Sum(tblCosts.CostFig) AS SumOfCostFig, Last(tblEmail.EmailDate) AS LastOfEmailDate
FROM ((tblstatus INNER JOIN ((tbllog INNER JOIN tbldept AS tbldept_1 ON tbllog.DeptRaisedBy = tbldept_1.DeptID) INNER JOIN tbldept ON tbllog.DeptResp = tbldept.DeptID) ON tblstatus.NCStatusID = tbllog.NCStatus) LEFT JOIN tblCosts ON tbllog.NCC_ID = tblCosts.NCC_ID) LEFT JOIN tblEmail ON tbllog.NCC_ID = tblEmail.NCC_ID1
GROUP BY tbllog.NCC_ID, tbllog.DteReport, tbllog.DteOccur, tbldept_1.Department, tbldept.Department, tblstatus.NCStatus;

The basic schematic is:

nonconformance ID and info (from tbllog) + sum of costs (from cost table, can be multiple costs) + last email date sent associated with nonconformance ID (can be multiple emails sent)


The issue I have is that everytime I send a new email (becoming the last sent date) my sum of costs becomes multiplied by the number of email records associated with the same nonconformance ID.

For example:

NCC ID 160 with £500 total cost (2x £250 records) and 3 emails sent

My query outputs that the total cost is £1500.

How can I change my sql to stop this multiplication happening?

[Stephen Parker]

Assuming that the rest of your SQL works the way you want it to then it appears as though you simply need to isolate the sum statement. In other words maybe try to perform the sum then join it to the rest of the select statement to prevent the calculation from multiplying on each email. That is to say if it is neccessary that you have all of the multiple emails returned by the query and isolating a unique instance is no optional then maybe try something like this:

SELECT 
tbllog.NCC_ID, 
tbllog.DteReport, 
tbllog.DteOccur, 
tbldept_1.Department AS DeptRaisedBy, 
tbldept.Department AS DeptResp, 
tblstatus.NCStatus, 
A.SumOfCostFig, 
Last(tblEmail.EmailDate) AS LastOfEmailDate 
FROM ((tblstatus 
INNER JOIN 
((tbllog 
INNER JOIN tbldept AS tbldept_1 
ON tbllog.DeptRaisedBy = tbldept_1.DeptID) 
INNER JOIN tbldept 
ON tbllog.DeptResp = tbldept.DeptID) 
ON tblstatus.NCStatusID = tbllog.NCStatus) 
LEFT JOIN (
SELECT NCC_ID, Sum(CostFig) AS SumOfCostFig
FROM tblCosts 
GROUP BY NCC_ID) AS A
ON tbllog.NCC_ID = A.NCC_ID) 
LEFT JOIN tblEmail ON tbllog.NCC_ID = tblEmail.NCC_ID1 
GROUP BY 
tbllog.NCC_ID, 
tbllog.DteReport, 
tbllog.DteOccur, 
tbldept_1.Department, 
tbldept.Department, 
tblstatus.NCStatus,
A.SumOfCostFig; 
 

[munkee]

Works perfectly Stephen thanks!