How do I merge these SELECT statements...

[Lyndon]

Hi guys,

I have the following two SQL statements...

SELECT Task.Task_Name, SUM(Logged_Time.Time_Spent)
FROM (Logged_Time INNER JOIN Task ON Task.Task_ID =
Logged_Time.Task_ID)
GROUP BY Task.Task_Name

SELECT Task_BU.Task_ID, Business_Unit.BU_Name, Task_BU.Split
FROM (Business_Unit INNER JOIN Task_BU ON Business_Unit.BU_ID =
Task_BU.BU_ID),
(SELECT MAX(Task_BU.Date) AS Last_Date FROM Task_BU GROUP BY
Task_BU.Task_ID)
WHERE Task_BU.Date = Last_Date

These are from a task management system. I want to multiply
SUM(Logged_Time.Time_Spent) from the first statement by Task_BU.Split
from the second statement but I can't figure out how to combine the
statements together. I have tried a few combinations but Access always
wants me to put all the select items (Task_Name, BU_Name, Split etc) in
the GROUP BY clause and this doesn't give me the results I want.

Basically all I want in the SELECT line is...

SELECT Task.Task_Name, Business_Unit.BU_Name, Task_BU.Split *
SUM(Logged_Time.Time_Spent)

So that I can multiply the splits for each task with the total time
logged against the task.

Thanks in advance...

Lyndon.

 

[tina]

well, if you work with your two posted SQL statements as separate queries,
and use them as the base of a third query, the following might work:

change the first query to

SELECT Task.Task_ID, Task.Task_Name, SUM(Logged_Time.Time_Spent)
FROM (Logged_Time INNER JOIN Task ON Task.Task_ID =
Logged_Time.Task_ID)
GROUP BY Task.Task_ID, Task.Task_Name

now you have a Task_ID field in each base query. see if the third, "linking"
query will return what you need if you link the two base queries on their
Task_ID fields.

hth