Dynamic Crosstab - Are they possible? 1

[Kutter]

Hi all,

I'm not really even sure how to word all this, let alone actually make Access do this for me. So please bare with me.

I am not a programmer, Access programming confuses the hell out of me as I cant find a basic enough tutorial to start to learn, although I need to do something that really shouldn't require it, my feeling on Access is that it will and it will be complicated.

Ok so here goes...

In Lotus approach you can create a crosstab query with major column headings (such as year 2003, 2004 etc) Under those as subheadings you can choose other data such as Sales totals, Order totals and you can have seperate columns for each piece of data repeated for EACH 'major heading' (oh these subheadings are also SUM totals of the data)

Then down the side you can have rows which show a period (period 1, 2, 3, 4 of the year) and then another row that will show individual dates (beginning to end of month!)

These crosstabs are also dynamic! So if you update the data underlying the table (such as add 2005) the crosstab does not need to be rewritten.

Essentially Approach can be used as an expensive analysis tool in this respect, but my question is...

Can Access do it?

Thx in advance,

Kutter.

 

[BobJacksonNCI]

Kutter,

Probably. (Access can do it)

Why not use the query wizard to walk through the process?


HTH,
Bob

 

[Kutter]

Because the wizard wont let you have more than one column Bob, thats why I asked.

Thx,

Kutter

 

[Kutter]

Appologies, that came across as a bit harsh... What I actually mean is that Access wizard wont allow me to pick 2 'different' criteria as Heading AND subheading for the Column. Example

2003 2004
Sales Orders Sales Orders

The sales and orders columns need to be sum totals also and dow the side (the rows) need to be dates

Thx,
Kutter.

 

[BobJacksonNCI]

Kutter,

No offense taken - Access can be frustrating.

Honestly, I've only written one crosstab query and thankfully it satisfies my need.

To get the most useful answer from the gurus here, I recommend you run the query wizard. Convert the design view to SQL using the button and post the SQL here. Someone here, maybe even me, may be able to suggest how to add the subheadings in SQL form.



HTH,
Bob

 

[DUF]

Hi,
I your CrossTab Query put in the following
Field = Expr1:Format([Date],"yyyy")
Table =
Total = Group By
CrossTab = Colum Heading
and next enter
Field = Sales Order
Table = tblSales (your table name)
Total = Sum
CrossTab = Value

That should do it

Regards

Duf

 

[losthorizon]

Crystal Reports, version 8.5 (dated now, but what I'm currently using) has a cross-tab wizard. However, I've only done tutorial / virtual learning stuff with it; & never really used it in an actual business / job situation. Crystal emphasizes how important their usage is, but we don't use them here, at all! Although it appears to be a simple wizard to use, I quickly ran into some complications actually trying to build a cross-tab report...when I ran the report, the PC ran out of memory, and I had to reboot to escape out of it! "Too many columns & rows," was the warning I saw. I eliminated some rows & columns, but it still overburdened the PC's memory. Gave up!

 

[dhookom]

There is a solution for multiple value crosstabs in faq701-4524. In addition, you can possibly use pivot tables although I don't have much experience with them. There are samples of crosstab reports at

http://www.invisibleinc.com/divFiles.cfm?divDivID=4

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[losthorizon]

Thanks! I'll give that a look.