Help needed with this running totals crosstab query....

[AlexT]

Folks

I have a table that lists sales by quarter and object type

stQtr ! stValue ! stObj !
----------------------------------
1 ! 100 ! gadget !
1 ! 50 ! widget !
2 ! 20 ! gadget !
2 ! 25 ! widget !
3 ! 5 ! widget !

I'd like to create a cross tab query that lists the *running* total of
sales per object type, ie


! Qtr1 ! Qtr2 ! Qtr3 !
-----------------------------------
gadgets ! 100 ! 120 ! 120 !
widgets ! 50 ! 75 ! 80 !


I have browsed through various material including this NG and I
understand that I have to calculate those running sum before running my
crosstab. However I'm not sure how to prepare that "base" query....

I have tried to create qSum as

SELECT Sales.stQtr, Sales.stObj, Sum(Sales.stValue) AS SumOfstValue
FROM Sales
GROUP BY Sales.stQtr, Sales.stObj;

And then

TRANSFORM Sum(qSum.SumOfstValue) AS SumOfSumOfstValue
SELECT qSum.stObj
FROM qSum
GROUP BY qSum.stObj
ORDER BY qSum.stQtr
PIVOT qSum.stQtr;

But that's not the trick...

Any help welcome

Regards

--alexT

 

[Gary Walter]

I have a table that lists sales by quarter and object type

stQtr ! stValue ! stObj !
----------------------------------
1 ! 100 ! gadget !
1 ! 50 ! widget !
2 ! 20 ! gadget !
2 ! 25 ! widget !
3 ! 5 ! widget !

I'd like to create a cross tab query that lists the *running* total of
sales per object type, ie


! Qtr1 ! Qtr2 ! Qtr3 !

<snip>

Hi Alex,

A typical running sum uses a subquery

SELECT
Sales.stQtr,
Sales.stValue,
Sales.stObj,
(SELECT Sum(S.stValue)
FROM Sales AS S
WHERE S.stObj=Sales.stObj
AND
S.stQtr<=Sales.stQtr) AS RS
FROM Sales;

which given your example data won't
fully meet your needs I believe:

qryRunningSumSales stQtr stValue stObj RS
1 100 gadget 100
1 50 widget 50
2 20 gadget 120
2 25 widget 75
3 5 widget 80


You may possibly end up missing a
Qtr/Obj sum, like in your example

3 0 gadget 120

Nevermind that for now, if we
try to run a crosstab off of
"qryRunningSumSales"

TRANSFORM Max(Q.RS) AS RunSum
SELECT Q.stObj
FROM qryRunningSumSales AS Q
GROUP BY Q.stObj
PIVOT Q.stQtr;

crosstabs can have problem w/subqueries
and will probably choke with error msg

"The Microsoft Jet database engine
does not recognize 'Sales.stObj'
as a valid field name or expression."

So....I have to first ask if....

1) Is this query for a report?

There are sometimes easier ways to
get a "running sum" in a report.

If it is for a report, how do you "see"
the resulting page(s)?

2) Will you always expect a specific
set of "stQtr's" in your data?

I imagine you wanted to keep your
sample data simple, but I would imagine
a real table with date fields and "qtrs"
calculated over years.

The reason I ask this is because you
can "roll your own" crosstabs in certain
situations.

For example, if you knew that your
table Sales would only have the set
of stQtrs (1,2,3,4)...


SELECT
Sales.stObj,

(SELECT Sum(S.stValue)
FROM Sales AS S
WHERE S.stObj=Sales.stObj
AND
S.stQtr = 1) AS Qtr1,

(SELECT Sum(S.stValue)
FROM Sales AS S
WHERE S.stObj=Sales.stObj
AND
S.stQtr <= 2) AS Qtr2,

(SELECT Sum(S.stValue)
FROM Sales AS S
WHERE S.stObj=Sales.stObj
AND
S.stQtr<= 3) AS Qtr3,

(SELECT Sum(S.stValue)
FROM Sales AS S
WHERE S.stObj=Sales.stObj
AND
S.stQtr <= 4) AS Qtr4

FROM Sales
GROUP BY Sales.stObj;

which would give from your
sample data:

qryRunningSalesRollYourOwnCrosstab stObj Qtr1 Qtr2 Qtr3 Qtr4
gadget 100 120 120 120
widget 50 75 80 80



That does not mean you are out of luck
in the original method above.

You could create a query that enumerates
all distinct Qtrs/Objs, left join this query to original
Sales to get RS with subquery, and save result in a table.
Then run crosstab on saved table.
That is possible if you need help with it...

good luck,

gary

 

[AlexT]

Hello

Thanks for message - it did provide some good hints, although I'm
still struggling to obtain the desired result...

So....I have to first ask if....

1) Is this query for a report?

Yes, definitely !

There are sometimes easier ways to
get a "running sum" in a report.

Indeed, but I don't really know how to tackle the problem of the
dynamic nature of the crosstab (ie one never knows how many columns /
rows will exist...)

If it is for a report, how do you "see"
the resulting page(s)?

Column : Dates
Rows: Products

Typically

! Qtr1 ! Qtr2 ! Qtr3 ! ... Qtr N
-----------------------------------
gadgets ! 100 ! 120 ! 120 !
widgets ! 50 ! 75 ! 80 !

2) Will you always expect a specific
set of "stQtr's" in your data?

Well, yes and no... In my specific implementation those are actually a
discrete set of dates (not really quarters) and the user might add new
dates at any given time.

I'm wondering about coding this into Excel...

Regards

--alexT

 

[Gary Walter]

Sorry for delay Alex in responding .....

This probably is not what you want to hear...

If I were faced with your situation,
I would transpose my report structure to

gadgets RSgadgets widgets RSwidgets
Qtr1 100 100 50 50
Qtr2 20 120 25 75
Qtr3 0 120 5 80
.....
QtrN

what is the difference between textbox for
gadgets (which you don't have to show)
and textbox for RSgadgets?

Just setting the Running Sum property.

I simply would not attempt a report where
I don't know how "wide" the field structure
will be, which I think I hear you saying would
be the case if Qtr's were the column headings.

Are possible gadgets/widgets a discrete set?

If not, then you probably should provide more
detail:

-- how will the user set report date intervals
-- how do you get the data like your sample data
- a query on one or more tables?
- what was the query's SQL?
- what are the table(s) fields and type?
-- if you must use a crosstab, then I believe you
are going to have to say "only this many Qtr's
for a report"...no arbitrary N Qtr's.

or you might look at Duane's example db
that allows you to "have any number of columns
without changing the design of any objects or code."


http://www.invisibleinc.com/download.cfm?filFilID=8

I'm sorry...I have to get to work...

good luck,

gary