Adding SUM'ed values from 2 tables

[russgreen]

I have a tricky SQL statement to write and not sure if it's even possible

I have the following 3 tables and fields:

tblProjects
projectID
taskID
etc....

tblTasks
taskID
taskName
etc...

tblTiming
timingID
taskID
hoursworked
etc...

tblExpenses
expenseID
taskID
expenseamount
etc...

What I want to do is display the SUM of both hoursworked and expenseamount as ActualCost

I've tried this SQL statment but it doesn't add the 2 summed values together.

SELECT (SELECT SUM(tblTiming.TotalCost) AS SumCost FROM tblTiming WHERE tblTiming.TaskID=tblTasks.TaskID;) + (SELECT SUM(tblExpenses.Amount) As SumExpense FROM tblExpenses WHERE tblExpenses.TaskID=tblTasks.TaskID;) AS ActualCost
FROM tblTasks
WHERE (((tblTasks.ProjectID)=[@ProjectID]));

Any suggestions



Regards,
Russ

http://www.learnvisualstudio.net?referedby=38523

 

[SkipVought]

How about this...

SELECT SUM(tblTiming.TotalCost) + SUM(tblExpenses.Amount) As ActualCost

FROM tblTasks, tblTiming, tblExpenses
 
WHERE (((tblTasks.ProjectID)=[@ProjectID]))
  AND tblTiming.TaskID      =tblTasks.TaskID
  AND tblExpenses.TaskID    =tblTasks.TaskID;

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[dhookom]

I confused a little since you seem to be mixing SQL Server syntax (@ProjectID) and JET syntax ";". If something doesn't work you should tell us a little about your results and what you expected.

SELECT 
  (SELECT SUM(tblTiming.TotalCost)
   FROM tblTiming
   WHERE tblTiming.TaskID=tblTasks.TaskID) + 
  (SELECT SUM(tblExpenses.Amount)
   FROM tblExpenses
   WHERE tblExpenses.TaskID=tblTasks.TaskID) AS ActualCost
FROM tblTasks
WHERE tblTasks.ProjectID=@ProjectID;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[russgreen]

thanks for the responses.

actually I discovered a few things.

1. i was getting problems becuase both summed values didn't necessarily return a value. so I used this and it worked within access

SELECT (SELECT SUM(tblTiming.TotalCost) AS SumCost FROM tblTiming WHERE tblTiming.TaskID=tblTasks.TaskID;) ActualCostHours, (SELECT SUM(tblExpenses.Amount) As SumExpense FROM tblExpenses WHERE tblExpenses.TaskID=tblTasks.TaskID;) AS ActualCostExpenses, nz(ActualCostHours, 0) + nz(ActualCostExpenses, 0) AS ActualCost
FROM tblTasks
WHERE (((tblTasks.ProjectID)=[@ProjectID]));

2. problem is i'm calling this stored procedure from VB.NET and nz generates an exception so in the end and added the ActualCostHours and ActualCostExpenses within my code.

3. I know JET SQL doesn't support named parameters but I just find it easier to keep track of this this way. It seems to work OK. What are the issues of doing it this way?



Regards,
Russ

http://www.learnvisualstudio.net?referedby=38523