Results 1 to 2 of 2
  1. #1

    SQL changes in queries (2000)

    I work with an Access database that was converted from 97 to 2000 when my employer upgraded to Office 2000. It is quite a complex database, containing numerous tables, queries, forms, reports, and modules.

    I was producing the reports this week (I produce reports annually from the database) when one report would not work - I got an error dialog box. I tracked back to the queries underlying the report and - in a nutshell - the problem was caused by a union query referencing two crosstab queries in a subquery. (The other two subqueries were from select queries so they weren't causing any problems). The error message was along the lines of "cannot reference non-fixed column in a crosstab".

    After doing a Goggle search for the text of the error (no exact hits) and after going to Microsoft tech support (where I did get some help), I found that in Access 2000 you cannot use a crosstab query as a subquery. (I fixed the problem by running a select query off each crosstab query and then using these new select queries in the union query).

    I have two questions:
    1. The union query worked okay in Access 97. Is this inability to use a crosstab query as a subquery something new in Access 2000?

    2. Is there any documentation on the web that lets me know the changes that have occurred between Access 97 and 2000 at the SQL (and VB) level? I would really love to remove potential problems like this one before they occur.

    cheers in advance from a chilly (8 degrees C) Wellington, New Zealand <img src=/S/surrender.gif border=0 alt=surrender width=31 height=23>

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Sacramento, California, USA
    Thanked 1 Time in 1 Post

    Re: SQL changes in queries (2000)

    Try Microsoft knowledgebase article "ACC2000: New Features in Microsoft Jet 4.0" #Q275561.

    As for the crosstab query, the error message is specific. If you add fixed column headings to your crosstab, you'll be able to continue using it.

Posting Permissions

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