Results 1 to 6 of 6
  1. #1
    DCBaker is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2021
    Posts
    3

    Crosstab Query Error--converts "Group By" to "Expression" then closes window

    I have worked with many crosstab queries over the years and never encountered what is happening the past two days. I've created several crosstabs--one, then two others copied from it. All were working, then all stopped letting me in. With each, when I attempt to go into the Design view, I get:



    You must enter Group By in the Total row for a field that has Column Heading in the Crosstab row.


    I did have Group By in that field, but it has been changed to "Expression." It won't let me in to change it. When I click OK on the error message, it closes the query. There is no option to view the SQL version.

    But it is complicated further by the fact that I saw this on the original query yesterday and eventually somehow got it to let me in. I changed it back to Group By then, and it worked until today when it was changed again--not only in this query but in the ones I'd copied from it.

    I don't know if it could be affecting this, but I have a second query pulling from this one, and updating a table, based on it. The second query still works--pulling the data from the bad query. But I want to change something in the bad query and can't do it.

    What can I try?

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Did you try right clicking on the query in the nav pane and choosing "design" or "sql" view so that you could fix it? If that doesn't work either, then you can get the sql property of the query via vba code and then pasting that into a new query where you fix it. The 'works then doesn't work' thing is a puzzle. Could someone be altering this query without you knowing?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    DCBaker is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2021
    Posts
    3
    Hi, Micron. The SQL option is not available--just to view or design. Both the view and design options give the same error and close the window when I click OK. I am finding that others have reported this same problem on various forums for years now and no one has ever posted a solution that I have found. I am going to try reinstalling Access and running a thorough virus check on my computer. But any other guidance would be much appreciated.

    Oh, and I forgot to describe my setup yesterday. I am on Windows 10 Home, version 1909. I use Access v. 2008 (Build 13127.21348).

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Well, that's one out of two suggested options. Reinstalling is a third, I guess. I'm using Access 2016 (365) version 2010 so it seems you're a bit behind me, but I've never had this problem in any version. Good luck!
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    DCBaker is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2021
    Posts
    3

    Did a Redo

    Quote Originally Posted by Micron View Post
    Well, that's one out of two suggested options. Reinstalling is a third, I guess. I'm using Access 2016 (365) version 2010 so it seems you're a bit behind me, but I've never had this problem in any version. Good luck!
    I am so sorry I apparently read past the second option, Micron. I reinstalled, to no effect. Then I decided that maybe the problem was that I was running a crosstab with another crosstab as its data source. I redesigned the query without that complexity and haven't had a problem since that. In the process of trying to find a solution, I did find where others had hit on the same problem over the past ten years or more, and no one had ever posted a solution. So I don't know that the crosstab on a crosstab was the real problem--only that I'm working again.

    Thanks for your help!

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Glad you solved it. I have never thought of querying a xt with a xt - seems counter intuitive to me. Select query on xt, sure, but pivoting pivoted data? That's a new one to me.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 06-06-2018, 08:26 PM
  2. Replies: 4
    Last Post: 12-20-2015, 12:16 PM
  3. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  4. Replies: 8
    Last Post: 07-15-2014, 05:56 PM
  5. "Group By" causes "ODBC--Call Failed" error
    By kaledev in forum Queries
    Replies: 1
    Last Post: 03-09-2011, 02:43 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums