# need help with access query

I have a simple table,

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
###### Who is Participating?

Database 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
, Sum([t].[averagenum2]) AS num2A
FROM Table1 AS t
``````

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

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

MIS 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

Senior 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
``````
0

Commented:
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?

From YourTable;
0

Commented:
Hi,

you can do that using Sub-SELECTs.

``````SELECT TOP 1
FROM SimpleTable
``````

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
``````

Cheers,

Christian
0

Database and Application DeveloperCommented:
``````SELECT Sum([t].[averagenum1]) AS num1A
, Sum([t].[averagenum2]) AS num2A
FROM Table1 AS t
``````

This post has been revised...

Mike
0

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

``````Dim strYourString as string
me.txtString=strYourString
``````

You can now send this string wherever you need.
0

Author 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.