Re: Crosstab REPORT, not query

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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?

.