DSum or Sum in a Crosstab query

[StefanScheidegger]

Hi everybody

I have a crosstab in Access that needs to display a running total per day broken out by product.

The current crosstab-query that I have produced is the following:

PRODUCT1 PRODUCT2 PRODUCT3
01.01.2003 100 400 300
02.01.2003 600 700 1100
03.01.2003 200 2400 200
etc.


The crosstab-Query that I would like to produce is this one:

PRODUCT1 PRODUCT2 PRODUCT3
01.01.2003 100 400 300
02.01.2003 700 1100 1400
03.01.2003 900 3500 1600
etc.

Do you have any ideas to do that?

Any help is appreciated. Thanks a lot for your support.

Stefan

 

[zerosandones]

Hi Stefan,

Here's a trick I've used in many similar situations across the years ... just substitute the field names in your table for the one's I've used here:

TRANSFORM Sum(tblProduct_1.ProductTotal) AS SumOfProductTotal
SELECT tblProduct.ProductDate
FROM tblProduct INNER JOIN tblProduct AS tblProduct_1 ON tblProduct.Product = tblProduct_1.Product
WHERE (((tblProduct_1.ProductDate)<=[tblProduct]![ProductDate]))
GROUP BY tblProduct.ProductDate
ORDER BY tblProduct.ProductDate, tblProduct.Product
PIVOT tblProduct.Product;

Notice that this solution is based upon linking your table to a second copy of itself by the product name only. The WHERE clause is used to perform an aggregate SUM of the product totals for records in the secondary copy of the table at or before the date from the primary copy of the table.

Hope this helps,

00001111s

 

[StefanScheidegger]

Thanks very much for this answer. Does this also work if I tell you the following?


Product 1, Product 2 and Product 3 are entered by the user into the field [Product].

 

[zerosandones]

If I'm understanding you correctly, you're wondering if this solution is dynamic i.e. if the user enters any value into the tblProduct table for the Product field, will this solution handle this new value and create new columns as necessary?

The answer is yes.

By default, a crosstab query will &quot;Pivot&quot; each unqiue value (in this case your product names) into a column of its own. It will also remove a column from the output if your WHERE clause has filtered it out e.g. if you were only looking for a specific subset of products to be reported on.

Also by default, those pivoted values are sorted ascending from left to right. You can, however, control the sorting and preserve or limit fixed column headings (even when their is no record to support the pivot of a column). You do this by entering fixed &quot;Column Headings&quot; which can be defined in the properties of a query e.g. you could enter &quot;Product3&quot;,&quot;Product2&quot; if you only wanted to output those two products ... this would also cause these two columns to appear in the same left-to-right order you specify, in this case Product3 then Product2.

One final note, creating a &quot;Report&quot; based on a cross-tab query is a whole new ball game. If fixed (non-dynamic) column headings are acceptable, then there are no issues to deal with in a report. If, however, your column headings are expected to be dynamic, you need to write VB code or use temporary tables to pre-process your data for inclusion into the report. I've done both before ... they both have their advantages and disadvantages. Good rule of thumb here and for any other cost/benefit analysis, if there is no value added with incorporating this crosstab query into a report, don't do it.

Hope this helps,

00001111s