Results 1 to 2 of 2
2007-01-31, 23:59 #1
- Join Date
- Jan 2001
- Littleton, Colorado, USA
- Thanked 0 Times in 0 Posts
Crosstab Query and Changing Data (E2K, SP-3)
Because everyone at work wants to use Excel, I have written a crosstab query in MS Query to pull data from an access database. It has to be a crosstab to avoid running past 256 columns, so I had to split the data into multiple tabs. The problem is, when the query updates, it is possible for not only the number of rows to change but also the number of columns. The query properties give options for dealing with a changing number of rows, but is not as accomodating with changing columns. I haven't had a problem when the number of columns increases, but when it decreases, I sometime get #REF# errors in the formula bars in the topmost rows.
Is there anyone whose had this problem and knows a way to get around it?
Thanks in advance
2007-02-01, 00:16 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 Posts
Re: Crosstab Query and Changing Data (E2K, SP-3)
I don't know if fixed column headings will work for you. Just like in Access, you can fix the column headings:
PIVOT OtherField In (1,3,5,7,9,11)
The query will always return the specified columns in the specified order; missing data will be Nulls and data in column headings that aren't listed will be discarded.