Roll Up\Cube\Compute aggregate funct. in Access?

donnie200 · January 22nd, 2005, 05:18 PM

Hi everyone!

Is it possible to use Roll Up\Cube\Compute aggregate functions in Access SQL? Roll Up\Cube\Compute can be used in T-SQL but I am having problems to find any examples of Access SQL code utlizing those three functions. Or maybe Access has its own syntax to accomplish things like subtotals lines? Any syntax and sample code help is greatly appreciated!

Thank you.

Bob Bedell · January 22nd, 2005, 11:07 PM

Access SQL doesn't support any of the three operators you mention, but it does support the TRANSFORM statement, which T-SQL does not support, and which can be used to create cross tabulated result sets with summary rows. Might find it useful.

Bob

donnie200 · January 24th, 2005, 02:28 PM

Thanks Bob for your advice. I can come up with a query like the one below that can create a crosstab table. However, I have not been able to create totals per columns as in the report example (values:1,600,925, 2,525) below the query. Is there anything in Access SQL that would allow me to accomplish this? Thanks for your help!
--------------------------------------------------------------
TRANSFORM Sum(UnitSoldByRegion.NetQuantity) AS SumOfNetQuantity
SELECT UnitSoldByRegion.ProductDivisionName, UnitSoldByRegion.ProductFamilyName, Sum(UnitSoldByRegion.NetQuantity) AS [Total Of NetQuantity]
FROM UnitSoldByRegion
GROUP BY UnitSoldByRegion.ProductFamilyName
PIVOT UnitSoldByRegion.SalesBigArea;

-----------------------------------------------------------------

# of Units Reported Per Region
--------------------------------------------------------------
Product Family EMEA N. America Total
--------------------------------------------------------------
Accounting Basic 200 100 300
Accounting Pro w/Publisher 0 15 15
Accounting Professional 0 600 600
Accounting Small Business 200 200 400
Accounting Big Business 1,200 10 1,210
--------------------------------------------------------------
Total Per Region: 1,600 925 2,525

Bob Bedell · January 24th, 2005, 03:13 PM

Unfortunately, this is where Access SQL hits the wall. You could, however, generate an Access Report in the format you're looking for using the crosstab query as the Reports recordsource. The crosstab data would populate the reports Detail section. Then you would need to use unbound textbox controls in the Reports footer, and a bunch of VBA event handlers to calculate grand totals, write them to the footer controls, and hide any unneeded text boxes. Its a lot of work.

- Bob

donnie200 · January 24th, 2005, 06:11 PM

Thanks Bob for your valuable information. I would just have one more question. Do you think that creating an .ADP project in Access with SQL Server 2000 as backend db wouldb be a good solution to accomplish my crosstab report? Or having Access frontend with pass through queries/stored procedures in T-SQL getting data from SQL Server 2000 would even better? What do you think?

Bob Bedell · January 24th, 2005, 09:50 PM

I think this might actually get you closer, and its all Access SQL. It uses a union query comprising a bunch of subqueries. Both row and column totals are given with a summary row at the bottom. The results look like:

Code:

Color           DarkTotal        LightTotal   ColorTotal
Red                     3                 3            6
Green                   3                 3            6
Blue                    2                 4            6
  Totals                8                10           18

Here's my raw data:

Code:

    Color    Shade 
1,  "Red",   "Dark"
2,  "Red",   "Light"
3,  "Red",   "Light"
4,  "Red",   "Light"
5,  "Red",   "Dark"
6,  "Red",   "Dark"
7,  "Blue",  "Light"
8,  "Blue",  "Light"
9,  "Blue",  "Light"
10, "Blue",  "Light"
11, "Blue",  "Dark"
12, "Blue",  "Dark"
13, "Green", "Dark"
14, "Green", "Dark"
15, "Green", "Light"
16, "Green", "Light"
17, "Green", "Light"
18, "Green", "Dark"

And here is the union query:

Code:

SELECT     color,
                          (SELECT     COUNT(*)
                            FROM         Colors AS T1
                            WHERE      T0.color = T1.color AND Shade = 'Dark') AS DarkTotal,
                          (SELECT     COUNT(*)
                            FROM         Colors AS T2
                            WHERE      T0.color = T2.color AND Shade = 'Light') AS LightTotal,
                          (SELECT     COUNT(*)
                            FROM         Colors AS T3
                            WHERE     T0.color =T3.color) AS ColorTotal
FROM         Colors T0
UNION SELECT     '  Totals',
                          (SELECT     COUNT(*)
                            FROM         Colors
                            WHERE     shade = 'Dark') AS GrandDarkTotal,
                          (SELECT     COUNT(*)
                            FROM          Colors
                            WHERE      shade = 'Light') AS GrandLightTotal,
                          (SELECT     COUNT(*)
                            FROM          Colors) AS GrandTotal
FROM         Colors
ORDER BY color DESC;

Hope its a little closer to what you're looking for.

- Bob

Bob Bedell · January 24th, 2005, 10:49 PM

My major concern is I hope it isn't horribly slow if you're querying a lot of data. Thats a lot of subqueries, and you'll be adding several more. SQL Server can do cross-tabulation and pivot tables easily using the CASE statment or a stored procedure. Books-On-Line and Google are awash with examples. I was playing around with T-SQL's GROUPING and CUBE statments also, but the generated format is pretty odd.

- Bob

donnie200 · January 25th, 2005, 06:23 PM

Looks like a good solution. But I think I am going to write a store procedure in Transact SQL and pass paremeters to it from my Access frontend. I played a little bit with T-SQL code and Roll Up function and I got more or less what I want. Once again, thanks for your very helpful and thorugh advice. I appreciate it.