total multiple fileds into one


G

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

Advertisements

G

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

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;


ty said:
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.

ty said:
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.
 
G

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?


KARL DEWEY said:
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;


ty said:
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.

ty said:
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.
 
G

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;


ty said:
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?


KARL DEWEY said:
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;


ty said:
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.
 
J

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.

ty said:
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?


KARL DEWEY said:
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;


ty said:
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.
 
Ad

Advertisements

G

Guest

Thanks. Thant works.

John Spencer said:
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.

ty said:
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?


KARL DEWEY said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top