Re: Crosstab REPORT, not query

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


imani_technology_spam@xxxxxxxxx wrote:
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@xxxxxxxxx wrote:
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


imani_technology_s...@xxxxxxxxx wrote:
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@xxxxxxxxx wrote:
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?


imani_technology_spam@xxxxxxxxx wrote:
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?

aaron.kempf@xxxxxxxxx wrote:
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


imani_technology_spam@xxxxxxxxx wrote:
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?

.

Relevant Pages

  • Re: Future of ADPs
    ... to the latest & greatest technology that was ADP's. ... ADP, ... Personally I don't like the database design features of ADP's. ... Regarding linked SQL tables inside an mdb, ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Future of ADPs
    ... When I right-click and select new View, then I see a query builder. ... You both say that you prefer designing in SQL EM versus the ADP. ... Personally I don't like the database design features of ADP's. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Updating a SQL Dbase using values in specific Excel cells
    ... The ADP is what the users see. ... The Excel workbook is a quotation system provided by an external source ... values from the SQL database to a 'sheet in this workbook. ...
    (microsoft.public.excel.programming)
  • Re: Access 2007 ADP and SQL
    ... Yes its the same with SQL 2005. ... SQL2005 and repointed the ADP to the SQL2005 version, ... Sylvain Lafontaine, ing. ... for what seems to be every object in the database. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Future of ADPs
    ... in the adp, the best feature for me is the close link with the stored ... to the latest & greatest technology that was ADP's. ... Personally I don't like the database design features of ADP's. ... Regarding linked SQL tables inside an mdb, ...
    (microsoft.public.access.adp.sqlserver)