need help with access query

Guys, need your help
I have a simple table,
TaskName      averagenum1      averagenum2            
Task1                           3                 0            
Task2                           5                      9            
Task3                           6                      1            
Task4                           1                      2            
Task5                           2                       1            

i need to calculate four numbers:
num1A should be the total sum of every single number (averagenum1) for every Task but the Task 5, meaning----> 15 and
num1B should be the total sum  for  Task5 only  , meaning----> 2
num2A should be the total sum of every single number (averagenum2) for every Task but the Task 5, meaning----> 12 and
num2B should be the total sum  for  Task5 only , meaning----> 1



the result should be a single row with four digits :  15 2 12 1
rfedorovAsked:
Who is Participating?
 
Mike EghtebasDatabase and Application DeveloperCommented:
BTW, the output tested in my solution is:

num1A      num1B      num2A      num2B
15               2               12                  1

SELECT 
   Sum([t].[averagenum1]) AS num1A
 , (Select t2.averagenum1 From Table1 t2 Where t2.[TaskName]='Task5') AS num1B
 , Sum([t].[averagenum2]) AS num2A
 , (Select t3.averagenum2 From Table1 t3 Where t3.[TaskName]='Task5') AS num2B
FROM Table1 AS t
WHERE (((t.TaskName)<>'Task5'));

Open in new window



In my last solution I had Sum([t].[averagenum1]) AS num2A instead of Sum([t].[averagenum2]) AS num2A

But it is corrected above.

Mike
0
 
Shaun KlineLead Software EngineerCommented:
You can use the IIF(TaskName = "Task5", 0, averagenum1) for the first column.
You can use the IIF(TaskName = "Task5", averagenum1, 0) for the second column.
You can use the IIF(TaskName = "Task5", 0, averagenum2) for the third column.
You can use the IIF(TaskName = "Task5", averagenum2, 0) for the fourth column.

From there, you can use the Sum aggregate.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Single row?
Single row were?
Do you have a table where this will be stored?, ...what are the field names?
Can this just be a text string?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ste5anSenior DeveloperCommented:
E.g.

SELECT IIf([TaskName]="t5","t5","others") AS Expr1, Sum(T.Num1) AS SumOfNum1, Sum(T.Num2) AS SumOfNum2
FROM Table1 AS T
GROUP BY IIf([TaskName]="t5","t5","others");

Open in new window

0
 
PatHartmanCommented:
In your attempt to simplify, you haven't given us enough information.  Are you really hard-coding a task number or is there some other way to identify task 5?

Select Sum(IIf(TaskName = "Task5", averagenum1,0)) As Sum1Task5, Sum(IIf(TaskName = "Task5", 0,averagenum1)) As Sum1NotTask5,Sum(IIf(TaskName = "Task5", averagenum2,0)) As Sum2Task5, Sum(IIf(TaskName = "Task5", 0,averagenum2)) As Sum2NotTask5
From YourTable;
0
 
BitsqueezerCommented:
Hi,

you can do that using Sub-SELECTs.

SELECT TOP 1
    (SELECT Sum(averagenum1) FROM SimpleTable WHERE TaskName <> 'Task5') AS num1A,
    (SELECT Sum(averagenum1) FROM SimpleTable WHERE TaskName = 'Task5') As num1B,
    (SELECT Sum(averagenum2) FROM SimpleTable WHERE TaskName <> 'Task5') AS num2A,
    (SELECT Sum(averagenum2) FROM SimpleTable WHERE TaskName = 'Task5') As num2B
FROM SimpleTable

Open in new window


Access cannot run a SELECT without having a table so you need to add the TOP 1 and any table you want (here also "SimpleTable") which has at least one record. In SQL Server for example you would do the same on this way:

SELECT
    (SELECT Sum(averagenum1) FROM SimpleTable WHERE TaskName <> 'Task5') AS num1A,
    (SELECT Sum(averagenum1) FROM SimpleTable WHERE TaskName = 'Task5') As num1B,
    (SELECT Sum(averagenum2) FROM SimpleTable WHERE TaskName <> 'Task5') AS num2A,
    (SELECT Sum(averagenum2) FROM SimpleTable WHERE TaskName = 'Task5') As num2B

Open in new window


Cheers,

Christian
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
SELECT Sum([t].[averagenum1]) AS num1A
  , (Select t2.averagenum1 From Table1 t2 Where t2.[TaskName]='Task5') AS num1B
  , Sum([t].[averagenum2]) AS num2A
  , (Select t3.averagenum1 From Table1 t3 Where t3.[TaskName]='Task5') AS num2B
FROM Table1 AS t
WHERE (((t.TaskName)<>'Task5'));

Open in new window


This post has been revised...

Mike
0
 
Jeffrey CoachmanMIS LiasonCommented:
Create a form with a textbox(txtString) and a button(btnString),,...
On the button do something like this:

Dim strYourString as string
strYourString =Dsum("averagenum1","YourTable","TaskName<>" & "Task5") & "," & Dsum("averagenum1","YourTable","TaskName=" & "Task5") & "," & Dsum("averagenum2","YourTable","TaskName<>" & "Task5")  & "," & Dsum("averagenum1","YourTable","TaskName=" & "Task5")
me.txtString=strYourString 

Open in new window


You can now send this string wherever you need.
0
 
rfedorovAuthor Commented:
thank you Mike, works great. Will use
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.