Adding columns in a cross tab query

[Guest]

Im running a cross tab query. I would like to add an additional column which
would add (sum) several of the columns (but not all of them) generated by the
cross tab query. Need some help plz on how exactly to do this.

 

[Duane Hookom]

Create a new Row Heading in your crosstab that has the proper expression to
calculate what you want.

If you can't figure this out, you might want to provide some details.

 

[Guest]

Ok lets say that my cross tab query generated the following fields: A, B, C,
D, & E.

I want to add an additional column with an expression that would look
something like this

expr1: sum([A], , [D]); The result would be to give me A+B+D

Now I have tried to type in this above but it wont work. Im not sure how to
write the proper expression. What exactly do I need to type to get this to
work?

 

[Duane Hookom]

You have a field that you use to create your column headings. Since you
didn't provide this name, let's call it [FieldCol]. You also have a field
that provides your Sum Value which you didn't provide, let's call it
[FieldVal].

Your additional Row Heading would be:
Field: Sum(Abs([FieldCol]="A" or [FieldCol]="B" or [FieldCol]="D") *
[FieldVal])
Total: Expression
Crosstab: Row Heading

--
Duane Hookom
MS Access MVP
--

Ok lets say that my cross tab query generated the following fields: A, B,
C,
D, & E.

I want to add an additional column with an expression that would look
something like this

expr1: sum([A], , [D]); The result would be to give me A+B+D

Now I have tried to type in this above but it wont work. Im not sure how
to
write the proper expression. What exactly do I need to type to get this
to
work?

Create a new Row Heading in your crosstab that has the proper expression
to
calculate what you want.

If you can't figure this out, you might want to provide some details.

 

[Guest]

Alright i typed in the followoing

sub total: Sum(Abs([FieldCol]="A" or [FieldCol]="B" or [FieldCol]="D") *
[FieldVal])

My query is based on a paramater called [Customer], When I run the query it
should ask me for the name of the customer. Now the query asks me for the
name of the [Customer] along with [FieldCol] (so FieldCol is now a
parameter?). If I leave [FieldCol] blank then the column is blank on the
query. If I put A, B, or D as the second parameter then the result in row of
the new column sub total is the total of A, B, C, D, and E. If I leave it
blank or put in any other value then I get 0.

First of all I dont want [FieldCol] to be a parameter and second I dont want
total of A - E I just want a total of A+B+D.

You have a field that you use to create your column headings. Since you
didn't provide this name, let's call it [FieldCol]. You also have a field
that provides your Sum Value which you didn't provide, let's call it
[FieldVal].

Your additional Row Heading would be:
Field: Sum(Abs([FieldCol]="A" or [FieldCol]="B" or [FieldCol]="D") *
[FieldVal])
Total: Expression
Crosstab: Row Heading

--
Duane Hookom
MS Access MVP
--

Ok lets say that my cross tab query generated the following fields: A, B,
C,
D, & E.

I want to add an additional column with an expression that would look
something like this

expr1: sum([A], , [D]); The result would be to give me A+B+D

Now I have tried to type in this above but it wont work. Im not sure how
to
write the proper expression. What exactly do I need to type to get this
to
work?

Create a new Row Heading in your crosstab that has the proper expression
to
calculate what you want.

If you can't figure this out, you might want to provide some details.

--
Duane Hookom
MS Access MVP
--

Im running a cross tab query. I would like to add an additional column
which
would add (sum) several of the columns (but not all of them) generated
by
the
cross tab query. Need some help plz on how exactly to do this.

 

[Guest]

Ok lets just disregard my last post. I have now entered the expression
correctly.

sub total: Sum(Abs([FieldCol]="A" or [FieldCol]="B" or [FieldCol]="D")
*[FieldVal])

When I try and run the query though it tells me the following:

"This expression is typed incorrectly or it is too complex to be evaluated.
.. ."

If I take the " " marks off of A, B, and D then I can run the query however
the result in each row of my new column is zero.

Alright i typed in the followoing

sub total: Sum(Abs([FieldCol]="A" or [FieldCol]="B" or [FieldCol]="D") *
[FieldVal])

My query is based on a paramater called [Customer], When I run the query it
should ask me for the name of the customer. Now the query asks me for the
name of the [Customer] along with [FieldCol] (so FieldCol is now a
parameter?). If I leave [FieldCol] blank then the column is blank on the
query. If I put A, B, or D as the second parameter then the result in row of
the new column sub total is the total of A, B, C, D, and E. If I leave it
blank or put in any other value then I get 0.

First of all I dont want [FieldCol] to be a parameter and second I dont want
total of A - E I just want a total of A+B+D.

You have a field that you use to create your column headings. Since you
didn't provide this name, let's call it [FieldCol]. You also have a field
that provides your Sum Value which you didn't provide, let's call it
[FieldVal].

Your additional Row Heading would be:
Field: Sum(Abs([FieldCol]="A" or [FieldCol]="B" or [FieldCol]="D") *
[FieldVal])
Total: Expression
Crosstab: Row Heading

--
Duane Hookom
MS Access MVP
--

Ok lets say that my cross tab query generated the following fields: A, B,
C,
D, & E.

I want to add an additional column with an expression that would look
something like this

expr1: sum([A], , [D]); The result would be to give me A+B+D

Now I have tried to type in this above but it wont work. Im not sure how
to
write the proper expression. What exactly do I need to type to get this
to
work?

:

Create a new Row Heading in your crosstab that has the proper expression
to
calculate what you want.

If you can't figure this out, you might want to provide some details.

--
Duane Hookom
MS Access MVP
--

Im running a cross tab query. I would like to add an additional column
which
would add (sum) several of the columns (but not all of them) generated
by
the
cross tab query. Need some help plz on how exactly to do this.

 

[Duane Hookom]

How about provide the full sql view of the query that is too complex?

--
Duane Hookom
MS Access MVP

Ok lets just disregard my last post. I have now entered the expression
correctly.

sub total: Sum(Abs([FieldCol]="A" or [FieldCol]="B" or [FieldCol]="D")
*[FieldVal])

When I try and run the query though it tells me the following:

"This expression is typed incorrectly or it is too complex to be
evaluated.
. ."

If I take the " " marks off of A, B, and D then I can run the query
however
the result in each row of my new column is zero.

Alright i typed in the followoing

sub total: Sum(Abs([FieldCol]="A" or [FieldCol]="B" or [FieldCol]="D") *
[FieldVal])

My query is based on a paramater called [Customer], When I run the query
it
should ask me for the name of the customer. Now the query asks me for
the
name of the [Customer] along with [FieldCol] (so FieldCol is now a
parameter?). If I leave [FieldCol] blank then the column is blank on the
query. If I put A, B, or D as the second parameter then the result in
row of
the new column sub total is the total of A, B, C, D, and E. If I leave
it
blank or put in any other value then I get 0.

First of all I dont want [FieldCol] to be a parameter and second I dont
want
total of A - E I just want a total of A+B+D.

You have a field that you use to create your column headings. Since you
didn't provide this name, let's call it [FieldCol]. You also have a
field
that provides your Sum Value which you didn't provide, let's call it
[FieldVal].

Your additional Row Heading would be:
Field: Sum(Abs([FieldCol]="A" or [FieldCol]="B" or
[FieldCol]="D") *
[FieldVal])
Total: Expression
Crosstab: Row Heading

--
Duane Hookom
MS Access MVP
--

Ok lets say that my cross tab query generated the following fields:
A, B,
C,
D, & E.

I want to add an additional column with an expression that would look
something like this

expr1: sum([A], , [D]); The result would be to give me A+B+D

Now I have tried to type in this above but it wont work. Im not sure
how
to
write the proper expression. What exactly do I need to type to get
this
to
work?

:

Create a new Row Heading in your crosstab that has the proper
expression
to
calculate what you want.

If you can't figure this out, you might want to provide some
details.

--
Duane Hookom
MS Access MVP
--

Im running a cross tab query. I would like to add an additional
column
which
would add (sum) several of the columns (but not all of them)
generated
by
the
cross tab query. Need some help plz on how exactly to do this.