Trying to replace DSums by subquery

[Premy]

Hi there,

THE GIVENS:

The following query works fine:
SELECT tblProjectSteps.ProjectID, tblProjectSteps.StepID,...,
NZ(DSum("Total","tblPayments","ProjectID=" & [ProjectID] & " AND StepID =" & [StepID],0)+
NZ(DSum("Total","tblReceivables","ProjectID=" & [ProjectID] & " AND StepID =" & [StepID],0)+
NZ(DSum("Total","tblBankPayments","ProjectID=" & [ProjectID] & " AND StepID =" & [StepID],0) AS TotalCosts
FROM tblProjectSteps INNER JOIN tblSteps ON tblProjectSteps.StepID = tblSteps.StepID;

The 3 summed tables (tblPayments,tblReceivables,tblBankPayments) do all of course have a foreign key for ProjectID (from tblProjects) and StepID (from tblSteps) and the output would be something like

STEPID.....ROJECTID....TOTALCOSTS
1.............100.............1100
2.............100.............1200
3.............100...............900
4.............100.............1300
1.............200...............700
2.............200.............1400

and so on...

THE PROBLEM:

For performance issues I would want to eliminate the DSum part, i.e get me a TotalCosts field by other means . I tried several constructions with multiple queries and also subqueries but to no avail. Any suggestions from the query Cracks her would be greatly appreciated.

Thanks
Jaime,

 

[Darren Bartrup]

Would something like this point you in the right direction:

SELECT tblProjectID.ProjectID,
(SELECT SUM(TOTAL) FROM tblPayments WHERE tblPayments.ProjectID = tblProjectID.ProjectID) AS Total1,
(SELECT SUM(TOTAL) FROM tblReceivables WHERE tblReceivables.ProjectID = tblProjectID.ProjectID) AS Total2
FROM tblProjectID
GROUP BY ProjectID;

 

[Darren Bartrup]

Actually, got that wrong - missed the step bit, and you want a grand total:

SELECT tblProjectID.ProjectID, tblProjectID.StepID,
SUM((SELECT SUM(Total) FROM tblPayments WHERE ((tblPayments.ProjectID = tblProjectID.ProjectID) AND (tblPayments.StepID = tblProjectID.StepID))))+
SUM((SELECT SUM(Total) FROM tblReceivables WHERE ((tblReceivables.ProjectID = tblProjectID.ProjectID) AND (tblReceivables.StepID = tblProjectID.StepID)))) As GrandTotal
FROM tblProjectID
GROUP BY tblProjectID.ProjectID, tblProjectID.StepID;

 

[Premy]

Ok Darren I'll try it out and give u the FB later. thanks..

 

[Premy]

Darren,
Alas...
Please allow me to explain: let's say tblPayments looks like this
ProjectID......ProjectStep.....Total
100................1..................1000
100................2..................1000
200................1..................1000
200................3..................1000
And tblReceivables like this:
ProjectID......ProjectStep.....Total
100................1..................1000
100................2..................1000
200................1..................1000
200................4..................1000

whereas (for example) Dsum("Total","tblPayments","ProjectID="&[ProjectID &" And StepID="&[StepID]) + Dsum("Total","tblReceivables","ProjectID="&[ProjectID &" And StepID="&[StepID]) will return:
ProjectID......ProjectStep.....GrandTotal
100................1.....................2000
100................2.....................2000
200................1.....................2000
200................3.....................1000
200................4.....................1000

your query would return:
ProjectID......ProjectStep.....GrandTotal
100................1.....................2000
100................2.....................2000
200................1.....................2000
200................3.....................
200................4.....................

So apparently your query will only consider adding together rows which have the same ProjectID and StepID in BOTH tables.
My trials had already led me to similar results, hence my post. The only working solution I could think of so far is to create a separate table in which I'd append one row for every entry in the other tables, then query from this table, but I still hope there is a more elegant alternative to solve this puzzle.

Thanks for the attention
Jaime Premy

 

[Darren Bartrup]

Ok, think I've got it now:

SELECT tblProjectID.ProjectID, tblProjectID.StepID,
NZ(SUM(tblPayments.Total),0)+NZ(SUM(tblReceivables.Total),0) As GrandTotal
FROM (tblProjectID LEFT OUTER JOIN tblPayments ON ((tblProjectID.ProjectID = tblPayments.ProjectID) AND (tblProjectID.StepID = tblPayments.StepID))) LEFT OUTER JOIN tblReceivables ON ((tblProjectID.ProjectID = tblReceivables.ProjectID) AND (tblProjectID.StepID = tblReceivables.StepID))
GROUP BY tblProjectID.ProjectID, tblProjectID.StepID;

Based on your example this gives an answer of:
ProjectID StepID GrandTotal
100 1 2000
100 2 2000
100 3 0
200 1 2000
200 2 0
200 3 1000
200 4 1000

The only difference being where it shows a zero.

Edit: If you want to omit the 0's add this to the end of the query:
HAVING NZ(SUM(tblPayments.Total),0)+NZ(SUM(tblReceivables.Total),0)>0;

 

[Premy]

Darren I thank u for your patience and wizardly powers with queries. I'll try it out and get back to u.

 

[Darren Bartrup]

Hardly wizardly powers, but thanks for the compliment anyway.

Just finished a post-graduate course on database design and want to keep my brain & SQL understanding up to speed

 

[Premy]

Hi Darren I finally got it working the way I needed it to. The last example u gave me with left joins did have a problem (couldn't figure out yet why) when pulling data from more than 2 tables: rows get duplicated when ProjectID and StepID are same across different tables. But the left join business got me in the right direction anyway and what I finally did was create 1 query for each table, like this:
SELECT Sum(tblPayments.Total) AS SumTotal, tblProjectSteps.ProjectID, tblProjectSteps.StepID
FROM tblProjectSteps INNER JOIN tblPayments ON (tblProjectSteps.StepID= tblPayments.StepID) AND (tblProjectSteps.ProjectID = tblPayments.ProjectID)
GROUP BY tblProjectSteps.ProjectID, tblProjectSteps.StepID;

and so on for every table I need to pull data from. I then combined them in another query:
SELECT tblProjectSteps.ProjectID, tblProjectSteps.StepID, Sum(NZ(qryPayments.SumTotal,0))+Sum(NZ(qryReeceivables.SumTotal,0))+(...)+(...)+(...) AS GrandTotal FROM ((((((((tblProjectSteps LEFT JOIN qryPayments ON (tblProjectSteps.ProjectID = qryPayments.ProjectID) AND (tblProjectSteps.StepID = qryPayments.StepID)) LEFT JOIN qryReceivables ON... (u know the rest I'm sure).

I thus finally was able to replace my Dsums. I hoped some kind of ingenious subquery scheme would be possible, but these seperate queries scheme may even be better for performance, I guess. Performance DID dramatically improve anyways.

Thanks again for your patience in looking into the problem and putting me on the right track.

Regards,
Jaime

 

[Darren Bartrup]

Glad it helped point you in the right direction, even if it wasn't the complete answer you were after.

Darren