Crosstab query limitations

[Tomas C]

Howdy Folks,

I would like to be able to use the Access crosstab query function to
make a more human-readable table for me. However, there are two
limitations I run into:

1. Access will not let me use more than one column heading for a
crosstab query.

2. I don't believe that I can use text values for my "data" field,
although I am not sure of this. Excel suffers from this limitation too
and I don't know why that is.

So, with that in mind, can I use SQL and/or VBA to overcome these
obstacles? I don't need to calculate anything for my "data" field.

I can certainly provide details on the table in question, but before I
do that, I wanted to seek out some expert advice on what is possible.
Thank you in advance.

 

[Jerry Whittle]

1. Will Excel pivot tables allow more than one column heading? If so you
could export the data to Excel and run it from there.

2. I'm not sure what you are asking here. Please provide a brief example of
the data as it is now and how you would like that data displayed in a
Crosstab.

One thought: You shouldn't look at data in a table. Rather you should
present the data in forms and reports. This is especially true if other users
are going to be poking around in the database.

 

[Tomas C]

Howdy Jerry,

Thank you for your reply.

1. Will Excel pivot tables allow more than one column heading? If so you
could export the data to Excel and run it from there.

Yes, Excel will do that without a problem. However, Access is really
nice in that you can save your queries. If I can save an SQL statement
for future use that will cross tab my values for me, that would be
wonderful. I want to stay in Access for as long as I can before going to
Excel.

2. I'm not sure what you are asking here. Please provide a brief example of
the data as it is now and how you would like that data displayed in a
Crosstab.

I am dealing with water chemistry data, so I have results for compounds
that are detected and results for compounds that are not detected.
Non-detects show up <5 (or whatever the reporting limit is). In
addition, some values may have text qualifiers associated with them. In
that case, a value might appear as

<5 UJ

Excel will not pivot anything that is not numeric. I believe Access
cross tab queries are the same way, although I've read some things that
claim otherwise. I suspect that there's a way to do what I want through
SQL, although I'm not sure where to look.

Thus far, I've represented non-detects as negative numbers so I could
pivot them. I then used Excel's find and replace to swap the negatives
with less than signs. I'd love to be able to pivot the qualifiers, however.

One thought: You shouldn't look at data in a table. Rather you should
present the data in forms and reports. This is especially true if other users
are going to be poking around in the database.

Not to worry. The query output will eventually go in Excel. Access
reports have their place, but Excel is far for flexible in some cases,
especially with regard to formatting tables. No one is going to be
poking around in my database just to look at data.

 

[Tomas C]

I've been doing a lot of searching and it appears that this is almost an
unsolved problem. The other option is that my problem is not one that is
experienced by many people.

Excel will not crosstab text. Access will not crosstab more than one
column. OpenOffice Calc won't crosstab text, although DataPilot does
compare favorably to Excel's PivotTable function.

I suspect what I want can be accomplished by using VBA, or perhaps by
using something like SQL Server. However, I'm unfamiliar with both of
those tools. I will keep searching, but things are looking dim at the
moment.

 

[Duane Hookom]

It isn't too difficult to have more than one value returned in the crosstab
and returning text is also possible using "First" or "Last" or "Min" or "Max".

There is an example of how to create a multi-value crosstab at
http://www.tek-tips.com/faqs.cfm?fid=4524.

 

[Tomas C]

Howdy Duane,

Thanks for the link. The page appears to be down right now, but I will
check it out once it is up again.

Tomas

 

[Duane Hookom]

It worked for me just now.

 

[Tomas C]

It's working for me now, too. I got a Cold Fusion error the two times I
tried earlier. Thanks again.