Very Tricky Crosstab Query Question

[StefanSch]

I have a crosstab query with the following elements:

rows: date
columns: articles
values: numbers

The numbers are running sums calculated in another query.

NUMBERS TODAY:
The numbers in the crosstab query according to the state today are as following (example Article A):

5
6
9
14
etc.

TARGET NUMBERS:
What I want is that each number is increased by 2. This would be as follows:

7
10
15
22
etc.

Any Ideas how I can do that in this crosstab query?

 

[Rich]

http://www.access-programmers.co.uk/forums/showthread.php?s=&postid=167059#post167059

 

[Nouba]

Prepare the query that you are using in the cross with the appropriate calculations. I. e.

SELECT
  [date]
, articles
, numbers
, (SELECT
    Sum(numbers)
  FROM YourTable
  WHERE
    articles=T.articles AND
    [date]<=T.[date])+
    ((SELECT
      Count(*)
    FROM YourTable
    WHERE
      articles=T.articles AND
      [date]<T.[date])+1)*2 AS SumPlusCntTimesTwo
FROM YourTable AS T
ORDER BY
  articles
, [date]

BTW: date is a reserved word and shouldn't be used for a fieldname.

 

[StefanSch]

I will give this a try. Thanks

 

[StefanSch]

Question regarding the issue below. I had an idea. Should the following idea basically work?

In the position value I would write something like:

numbers + DSUM([FieldIncreaseNumber]).....

Numbers would be a given value (running sum calculated in another query).

The [FieldIncreaseNumber] should be a query field value (e.g. with the value 2 as in the example below) that would increase the field numbers.

I hope that this is more or less clear. Could you help me with the code?

 

[Nouba]

If your field numbers is already calculated you could try the following query

SELECT
  [date]
, articles
, numbers + ((SELECT
    Count(*)
  FROM YourTable
  WHERE
    articles=T.articles AND
    [date]<T.[date])+1)*2 AS IncreasedNumbers
FROM YourTable AS T
ORDER BY
  articles
, [date]

 

[StefanSch]

Thanks. I'll try that.