Crosstab REPORT, not query

[Guest]

Crystal Reports can create a crosstab report without an underlying
crosstab query. Does an Access 2003 ADP support a similar feature, or
do I have to use SQL Server 2005's PIVOT feature as a makeshift
crosstab query to feed the report? If I have to PIVOT, is there a way
to use with without an aggregate function?

 

[aaron.kempf]

you shouldn't report relationally-- you should report against a CUBE.
it is about a billion times more powerful than anything trivial like
Crystal's crosstab query.

by definition; Olap works best for MULTIDIMENSIONAL queries like this.

you can also hardcode a pivot if you're grouping by Months and year for
example.. year on rows; month on columns

-Aaron

 

[aaron.kempf]

Also; Reporting Services can do similiar crosstab queries relationally.

you should just adopt 'analysis services'

drag and drop and your end users can make their own reports-- they
don't need to bug you.

Analysis Services and Office Web Components fits QUITE well inside of
MS Access; you can just 'insert activex control' and then select
Microsoft Office PivotTable it should be called.

I've written a dozen intranet / reporting apps using Office Web
Components; but I would prefer to use them inside of MS Access Data
Projects.

I mean; you can't right-click SORT and right-click FILTER on a webpage;
nor in VB.net nor in VB6

-Aaron

 

[Guest]

Thanks to all of your for your advice. Please bear in mind that the
client has REQUIRED me to use MS Access as a front end. I don't think
they want to deal with anything else. In fact, they want the new app
to be as much like their old app as possible. Now, where can I find
more information about integrating Analysis Services or Reporting
Services with an MS Access ADP?

 

[Guest]

And here's another problem, so let me start from the beginning.

The legacy system consists of an MS Access MDB. The entire app is in
one file. One table in the legacy MDB is a crosstab TABLE, not a
crosstab query. That table is the source for one of the data entry
forms, which lists the crosstab table data in a crosstab fashion. So,
not only is it a crosstab table not a query, but it is feeding a data
entry form and not a report.

I created a normalized table in SQL Server and have successfully
migrated the data into the more properly normalized table. But now I
have to figure out how to crosstab (or de-normalize) that data for the
form and possibly for reports.

 

[aaron.kempf]

the client's REQUIREMENTS are dead-on.

Access is the best tool on the market. End of Story.

If it's a dump from a crosstab query into a table; that is one thing.
it really depends on how many columns it has??\

it's a different scope if it has 20 columns or 200.. for example

are you sure that you can't just write a normal query and then view it
in PivotTable view?

 

[Guest]

Actually, the TABLE in the database is set up as a crosstab. I already
normalized that table in the new SQL Server database and migrated the
data. I was just wondering if there was an easy way to take the new
table and get crosstab data out of it (like the original MS Access
table) for forms and/or reports.

 

[aaron.kempf]

assuming
myCrosstabTable = JanSales, FebSales, MarSales, etc

then try something like this

create view myNormalizedTable
AS
Select JanSales As DollarAmount, 1 as month, 'Sales' As MoneyType

From myCrosstabTable where JanSales IS NOT NULL

UNION ALL
Select FebSales As DollarAmount, 2 as month, 'Sales' As MoneyType

From myCrosstabTable where FebSales IS NOT NULL

UNION ALL
Select MarSales As DollarAmount, 3 as month, 'Sales' As MoneyType

From myCrosstabTable where MarSales IS NOT NULL

UNION ALL
Select AprSales As DollarAmount, 4 as month, 'Sales' As MoneyType

From myCrosstabTable where AprSales IS NOT NULL

rinse and repeat.

I usually use Excel for one thing and one thing only-- Formatting
repetitive SQL statements.

If the column headers are really really standardized; you could just
build this statement by querying against SysColumns I think.

HTH; I love ADP and I'd love to help out where I can.

-Aaron
ADP Nationalist

 

[Guest]

I appreciate the help. I haven't worked with an ADP since 2000. I'm
sure Access and the ADP have changed substantially since then. Are
there any books that you would recommend?

 

[aaron.kempf]

i'd mainly reccomend books online

and maybe having a 2nd instance of SQL 2005 Developers' edition.. so
you can use the 'Database Tuning Advisor' against the (production) SQL
2000 instance.

i have had _SOME_ problems with that setup; but altogether it's a great
tool for tuning SQL 2000.. I had a half dozen DBAs that had no problem
with it; I just know that it does a better job of creating indexes and
statistics than 2000 does... I'd just make sure you take backups of
the database before using 2005 DTA in a production environment of
course.

other books on ADP? I haven't seen very many books that go into enough
details about ADP.. i think that MS press has an ADP book circa 2003
that is a good choice.

i'd of course check out www.mvps.org/access that is a good site.

when it comes to deploying the runtime or anything; the
www.accessvba.com site was the best; but i think that it's gone now..
maybe www.archive.org can show you some good threads from that site??
there was definitely some decent ADP threads there; but the runtime /
deployment information on that site was 2nd to none.. I will miss that
site a lot.

other books; I've read a lot of them-- the 'Professional SQL Sever
development using Access 2000' had some good points.. but it's not
updated for Access 2002/2003; so it's kindof a waste.. that one was
'wrox press' it was written; I want to say buy a gentelman named
'David' i can't remember his real name though.

and then of course; usenet is a decent resource.. but a lot of the
people that you find here are mis-informed when it comes to ADP.

i think that ADP is the most revolutionary product that Microsoft has
ever released.

it kinda amazes me; i've seen a lot of companies using it recently.. I
just would tend to ignore people that are pro-mdb.

I think that MDB is 10 years obsolete.

-Aaron