total multiple fileds into one

[Guest]

I have a table with filed1, field2, field3, Field4, field5, etc. These are
numeric fields, except field1, which is text. I'd like to create a second
table with a TotalField which contains the aggregate of each field2 to field4
and it's grouped by field1.

 

[Guest]

This is what I've tried to do:

SELECT WorkVolume_tbl.Associate, [WorkVolume_tbl]![Custom
Print]+[WorkVolume_tbl]![Direct] AS Expr1, [WorkVolume_tbl]![Order Entry-
Mailbox]+[WorkVolume_tbl]![Order Entry-Fax] AS Expr2
FROM WorkVolume_tbl;

It doesn't work exactly as I'd like it to. I'd like Expr1 to also include
the fields added in Expr2. But as soon as I add a third field to the
calculation, it doesn't work.

 

[Guest]

What about this --
SELECT WorkVolume_tbl.Associate, [WorkVolume_tbl]![Custom
Print]+[WorkVolume_tbl]![Direct] +[WorkVolume_tbl]![Order Entry-
Mailbox]+[WorkVolume_tbl]![Order Entry-Fax] AS Expr1,
[WorkVolume_tbl]![Order Entry- Mailbox] +[WorkVolume_tbl]![Order Entry-Fax]
AS Expr2
FROM WorkVolume_tbl;

This is what I've tried to do:

SELECT WorkVolume_tbl.Associate, [WorkVolume_tbl]![Custom
Print]+[WorkVolume_tbl]![Direct] AS Expr1, [WorkVolume_tbl]![Order Entry-
Mailbox]+[WorkVolume_tbl]![Order Entry-Fax] AS Expr2
FROM WorkVolume_tbl;

It doesn't work exactly as I'd like it to. I'd like Expr1 to also include
the fields added in Expr2. But as soon as I add a third field to the
calculation, it doesn't work.

I have a table with filed1, field2, field3, Field4, field5, etc. These are
numeric fields, except field1, which is text. I'd like to create a second
table with a TotalField which contains the aggregate of each field2 to field4
and it's grouped by field1.

 

[Guest]

Thank. But it doesn't work. This works:
SELECT WorkVolume_tbl.Associate, [WorkVolume_tbl]![Custom
Print]+[WorkVolume_tbl]![Direct] AS Expr1
FROM WorkVolume_tbl;

This doesn't work:
SELECT WorkVolume_tbl.Associate, [WorkVolume_tbl]![Custom
Print]+[WorkVolume_tbl]![Direct]+[WorkVolume_tbl]![Order Entry-
Mailbox]+[WorkVolume_tbl]![Order Entry-Fax] AS Expr1
FROM WorkVolume_tbl;

Why can't I add more Fields to the expression?

What about this --
SELECT WorkVolume_tbl.Associate, [WorkVolume_tbl]![Custom
Print]+[WorkVolume_tbl]![Direct] +[WorkVolume_tbl]![Order Entry-
Mailbox]+[WorkVolume_tbl]![Order Entry-Fax] AS Expr1,
[WorkVolume_tbl]![Order Entry- Mailbox] +[WorkVolume_tbl]![Order Entry-Fax]
AS Expr2
FROM WorkVolume_tbl;

This is what I've tried to do:

SELECT WorkVolume_tbl.Associate, [WorkVolume_tbl]![Custom
Print]+[WorkVolume_tbl]![Direct] AS Expr1, [WorkVolume_tbl]![Order Entry-
Mailbox]+[WorkVolume_tbl]![Order Entry-Fax] AS Expr2
FROM WorkVolume_tbl;

It doesn't work exactly as I'd like it to. I'd like Expr1 to also include
the fields added in Expr2. But as soon as I add a third field to the
calculation, it doesn't work.

I have a table with filed1, field2, field3, Field4, field5, etc. These are
numeric fields, except field1, which is text. I'd like to create a second
table with a TotalField which contains the aggregate of each field2 to field4
and it's grouped by field1.

 

[Guest]

Try this --
SELECT WorkVolume_tbl.Associate, [Custom Print]+[Direct]+[Order Entry-
Mailbox]+[Order Entry-Fax] AS Expr1, [Order Entry- Mailbox]+[Order Entry-Fax]
AS Expr2
FROM WorkVolume_tbl;

Thank. But it doesn't work. This works:
SELECT WorkVolume_tbl.Associate, [WorkVolume_tbl]![Custom
Print]+[WorkVolume_tbl]![Direct] AS Expr1
FROM WorkVolume_tbl;

This doesn't work:
SELECT WorkVolume_tbl.Associate, [WorkVolume_tbl]![Custom
Print]+[WorkVolume_tbl]![Direct]+[WorkVolume_tbl]![Order Entry-
Mailbox]+[WorkVolume_tbl]![Order Entry-Fax] AS Expr1
FROM WorkVolume_tbl;

Why can't I add more Fields to the expression?

What about this --
SELECT WorkVolume_tbl.Associate, [WorkVolume_tbl]![Custom
Print]+[WorkVolume_tbl]![Direct] +[WorkVolume_tbl]![Order Entry-
Mailbox]+[WorkVolume_tbl]![Order Entry-Fax] AS Expr1,
[WorkVolume_tbl]![Order Entry- Mailbox] +[WorkVolume_tbl]![Order Entry-Fax]
AS Expr2
FROM WorkVolume_tbl;

This is what I've tried to do:

SELECT WorkVolume_tbl.Associate, [WorkVolume_tbl]![Custom
Print]+[WorkVolume_tbl]![Direct] AS Expr1, [WorkVolume_tbl]![Order Entry-
Mailbox]+[WorkVolume_tbl]![Order Entry-Fax] AS Expr2
FROM WorkVolume_tbl;

It doesn't work exactly as I'd like it to. I'd like Expr1 to also include
the fields added in Expr2. But as soon as I add a third field to the
calculation, it doesn't work.

:

I have a table with filed1, field2, field3, Field4, field5, etc. These are
numeric fields, except field1, which is text. I'd like to create a second
table with a TotalField which contains the aggregate of each field2 to field4
and it's grouped by field1.

 

[John Spencer]

Try using the NZ function to force a value for the fields if they are null.
Nulls propagate, so if any field in your calculation is NULL (blank) then
the result of the calculation is null.

SELECT WorkVolume_tbl.Associate
, NZ([WorkVolume_tbl].[Custom Print],0)+
NZ([WorkVolume_tbl].[Direct],0)+
NZ([WorkVolume_tbl].[Order Entry- Mailbox],0)+
NZ([WorkVolume_tbl].[Order Entry-Fax],0) AS Expr1
FROM WorkVolume_tbl

By the way, is there a space between the "-" and "Mailbox" in the field
"Order Entry- Mailbox"? If not, you will need to fix the proposed SQL
statement above.

Thank. But it doesn't work. This works:
SELECT WorkVolume_tbl.Associate, [WorkVolume_tbl]![Custom
Print]+[WorkVolume_tbl]![Direct] AS Expr1
FROM WorkVolume_tbl;

This doesn't work:
SELECT WorkVolume_tbl.Associate, [WorkVolume_tbl]![Custom
Print]+[WorkVolume_tbl]![Direct]+[WorkVolume_tbl]![Order Entry-
Mailbox]+[WorkVolume_tbl]![Order Entry-Fax] AS Expr1
FROM WorkVolume_tbl;

Why can't I add more Fields to the expression?

What about this --
SELECT WorkVolume_tbl.Associate, [WorkVolume_tbl]![Custom
Print]+[WorkVolume_tbl]![Direct] +[WorkVolume_tbl]![Order Entry-
Mailbox]+[WorkVolume_tbl]![Order Entry-Fax] AS Expr1,
[WorkVolume_tbl]![Order Entry- Mailbox] +[WorkVolume_tbl]![Order
Entry-Fax]
AS Expr2
FROM WorkVolume_tbl;

This is what I've tried to do:

SELECT WorkVolume_tbl.Associate, [WorkVolume_tbl]![Custom
Print]+[WorkVolume_tbl]![Direct] AS Expr1, [WorkVolume_tbl]![Order
Entry-
Mailbox]+[WorkVolume_tbl]![Order Entry-Fax] AS Expr2
FROM WorkVolume_tbl;

It doesn't work exactly as I'd like it to. I'd like Expr1 to also
include
the fields added in Expr2. But as soon as I add a third field to the
calculation, it doesn't work.

:

I have a table with filed1, field2, field3, Field4, field5, etc.
These are
numeric fields, except field1, which is text. I'd like to create a
second
table with a TotalField which contains the aggregate of each field2
to field4
and it's grouped by field1.

 

[Guest]

Thanks. Thant works.

Try using the NZ function to force a value for the fields if they are null.
Nulls propagate, so if any field in your calculation is NULL (blank) then
the result of the calculation is null.

SELECT WorkVolume_tbl.Associate
, NZ([WorkVolume_tbl].[Custom Print],0)+
NZ([WorkVolume_tbl].[Direct],0)+
NZ([WorkVolume_tbl].[Order Entry- Mailbox],0)+
NZ([WorkVolume_tbl].[Order Entry-Fax],0) AS Expr1
FROM WorkVolume_tbl

By the way, is there a space between the "-" and "Mailbox" in the field
"Order Entry- Mailbox"? If not, you will need to fix the proposed SQL
statement above.

Thank. But it doesn't work. This works:
SELECT WorkVolume_tbl.Associate, [WorkVolume_tbl]![Custom
Print]+[WorkVolume_tbl]![Direct] AS Expr1
FROM WorkVolume_tbl;

This doesn't work:
SELECT WorkVolume_tbl.Associate, [WorkVolume_tbl]![Custom
Print]+[WorkVolume_tbl]![Direct]+[WorkVolume_tbl]![Order Entry-
Mailbox]+[WorkVolume_tbl]![Order Entry-Fax] AS Expr1
FROM WorkVolume_tbl;

Why can't I add more Fields to the expression?

What about this --
SELECT WorkVolume_tbl.Associate, [WorkVolume_tbl]![Custom
Print]+[WorkVolume_tbl]![Direct] +[WorkVolume_tbl]![Order Entry-
Mailbox]+[WorkVolume_tbl]![Order Entry-Fax] AS Expr1,
[WorkVolume_tbl]![Order Entry- Mailbox] +[WorkVolume_tbl]![Order
Entry-Fax]
AS Expr2
FROM WorkVolume_tbl;


:

This is what I've tried to do:

SELECT WorkVolume_tbl.Associate, [WorkVolume_tbl]![Custom
Print]+[WorkVolume_tbl]![Direct] AS Expr1, [WorkVolume_tbl]![Order
Entry-
Mailbox]+[WorkVolume_tbl]![Order Entry-Fax] AS Expr2
FROM WorkVolume_tbl;

It doesn't work exactly as I'd like it to. I'd like Expr1 to also
include
the fields added in Expr2. But as soon as I add a third field to the
calculation, it doesn't work.

:

I have a table with filed1, field2, field3, Field4, field5, etc.
These are
numeric fields, except field1, which is text. I'd like to create a
second
table with a TotalField which contains the aggregate of each field2
to field4
and it's grouped by field1.