Results 1 to 2 of 2
  1. #1
    Star Lounger
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 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:

    TRANSFORM SomeField
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts