total multiple fileds into one

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ty - 21 Aug 2006 18:52 GMT

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.

Reply to this Message

ty - 21 Aug 2006 19:06 GMT

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.

Reply to this Message

KARL DEWEY - 21 Aug 2006 20:38 GMT

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:
>
[quoted text clipped - 11 lines]
> > table with a TotalField which contains the aggregate of each field2 to field4
> > and it's grouped by field1.

Reply to this Message

ty - 21 Aug 2006 21:30 GMT

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
[quoted text clipped - 19 lines]
> > > table with a TotalField which contains the aggregate of each field2 to field4
> > > and it's grouped by field1.

Reply to this Message

KARL DEWEY - 21 Aug 2006 23:33 GMT

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
[quoted text clipped - 32 lines]
> > > > table with a TotalField which contains the aggregate of each field2 to field4
> > > > and it's grouped by field1.

Reply to this Message

John Spencer - 22 Aug 2006 13:32 GMT

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
[quoted text clipped - 38 lines]
>> > > to field4
>> > > and it's grouped by field1.

Reply to this Message

ty - 22 Aug 2006 19:20 GMT

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
[quoted text clipped - 53 lines]
> >> > > to field4
> >> > > and it's grouped by field1.

Reply to this Message