Results 1 to 2 of 2
Thread: SQL changes in queries (2000)
2001-06-30, 09:11 #1magentaGuest
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>
2001-06-30, 14:41 #2
- 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.Charlotte