crosstab query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have created a crosstab query that displays information from a date.
eg:05/06
Partner 05/06 06/07 07/08
jec $200 $300 $400

the row is the partner, column heading is the date and the value is
[pcashflow_amt] by sum.

what I would now like to do is to calculate the sum of the amount based on
the following:

=sum(iif([pcashflow_operator]="add", 1-1)*[pcashflow_amt]

I want the sum as well as an accumulated sum from the expression above.
Where do I add this lime to my query?
 
How about trying again. 1-1 = 0. Did you mean 1, -1? Did you try your
expression in the Value column of the crosstab?
 
yes I meant 1, -1 and this works when I have it in the record source of a
report. I would like to add this to the crosstab query but not sure where to
put it? In the criteria of the value column?

Duane Hookom said:
How about trying again. 1-1 = 0. Did you mean 1, -1? Did you try your
expression in the Value column of the crosstab?

--
Duane Hookom
MS Access MVP


Tina said:
Hi,

I have created a crosstab query that displays information from a date.
eg:05/06
Partner 05/06 06/07 07/08
jec $200 $300 $400

the row is the partner, column heading is the date and the value is
[pcashflow_amt] by sum.

what I would now like to do is to calculate the sum of the amount based on
the following:

=sum(iif([pcashflow_operator]="add", 1-1)*[pcashflow_amt]

I want the sum as well as an accumulated sum from the expression above.
Where do I add this lime to my query?
Click to expand...
Click to expand...
 
If you want the expression as the value(s) in your crosstab, place it as the
field for the value:
TheValue:iif([pcashflow_operator]="add", 1,-1)*[pcashflow_amt]
Totals: Sum
Crosstab: Value

--
Duane Hookom
MS Access MVP
--

Tina said:
yes I meant 1, -1 and this works when I have it in the record source of a
report. I would like to add this to the crosstab query but not sure where
to
put it? In the criteria of the value column?

Duane Hookom said:
How about trying again. 1-1 = 0. Did you mean 1, -1? Did you try your
expression in the Value column of the crosstab?

--
Duane Hookom
MS Access MVP


Tina said:
Hi,

I have created a crosstab query that displays information from a date.
eg:05/06
Partner 05/06 06/07 07/08
jec $200 $300 $400

the row is the partner, column heading is the date and the value is
[pcashflow_amt] by sum.

what I would now like to do is to calculate the sum of the amount based
on
the following:

=sum(iif([pcashflow_operator]="add", 1-1)*[pcashflow_amt]

I want the sum as well as an accumulated sum from the expression above.
Where do I add this lime to my query?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top