If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"Group By" problem
We have a query that has been working quite happily for over ayear, but has
suddenly decided to play up, giving a "No current record" message box. The query is a Group By type, with just one of the items (hours) being "Sum". If we remove the Group By, the query works fine. We suspect a data problem, but cannot pinpoint it. Null is permitted in the hours field, and has been there in the data in the past. Any clues, please? JMCS |
#2
|
|||
|
|||
Hi.
The only time I've seen this happen with a query is when the table's index was corrupted. However, Jet will apparently choke on a Yes/No field in a GROUP BY query under special circumstances. (From your description, it sounds like there's only one column in your query, an aggregate function on a numerical data type, so this probably doesn't apply to you but it's something for future reference.) For more information on this, please see the following Web page: http://groups.google.com/group/micro...39994fe0c3a15c Have you tried a compact/repair recently? It's worth a try. If that doesn't help, try importing the tables and this query into a new database, remove the indexes, compact/repair, add the indexes again, and then run the query to see if you receive the same error in the new database. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips. (Please remove ZERO_SPAM from my reply E-mail address so that a message will be forwarded to me.) - - - If my answer has helped you, please sign in and answer yes to the question "Did this post answer your question?" at the bottom of the message, which adds your question and the answers to the database of answers. Remember that questions answered the quickest are often from those who have a history of rewarding the contributors who have taken the time to answer questions correctly. "JMCS" wrote: We have a query that has been working quite happily for over ayear, but has suddenly decided to play up, giving a "No current record" message box. The query is a Group By type, with just one of the items (hours) being "Sum". If we remove the Group By, the query works fine. We suspect a data problem, but cannot pinpoint it. Null is permitted in the hours field, and has been there in the data in the past. Any clues, please? JMCS |
#3
|
|||
|
|||
Thanks - I think you have pointed us in the right direction because - guess
what - we do have "Yes/No" fields in the query (I didn't mention it because I had assumed it was the field that was summed which caused the problem) and if we take those out, it works OK, albeit missing a little information which we can get another way. I will follow up the links you supplied. In the meantime thanks again. JMCS "'69 Camaro" wrote: Hi. The only time I've seen this happen with a query is when the table's index was corrupted. However, Jet will apparently choke on a Yes/No field in a GROUP BY query under special circumstances. (From your description, it sounds like there's only one column in your query, an aggregate function on a numerical data type, so this probably doesn't apply to you but it's something for future reference.) For more information on this, please see the following Web page: http://groups.google.com/group/micro...39994fe0c3a15c Have you tried a compact/repair recently? It's worth a try. If that doesn't help, try importing the tables and this query into a new database, remove the indexes, compact/repair, add the indexes again, and then run the query to see if you receive the same error in the new database. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips. (Please remove ZERO_SPAM from my reply E-mail address so that a message will be forwarded to me.) - - - If my answer has helped you, please sign in and answer yes to the question "Did this post answer your question?" at the bottom of the message, which adds your question and the answers to the database of answers. Remember that questions answered the quickest are often from those who have a history of rewarding the contributors who have taken the time to answer questions correctly. "JMCS" wrote: We have a query that has been working quite happily for over ayear, but has suddenly decided to play up, giving a "No current record" message box. The query is a Group By type, with just one of the items (hours) being "Sum". If we remove the Group By, the query works fine. We suspect a data problem, but cannot pinpoint it. Null is permitted in the hours field, and has been there in the data in the past. Any clues, please? JMCS |
#4
|
|||
|
|||
You're welcome. I considered leaving out the information about grouping on
the Yes/No field, because it didn't sound like it was pertinent to your situation. Now I'm glad I mentioned it. ;-) Good luck. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips. "JMCS" wrote in message ... Thanks - I think you have pointed us in the right direction because - guess what - we do have "Yes/No" fields in the query (I didn't mention it because I had assumed it was the field that was summed which caused the problem) and if we take those out, it works OK, albeit missing a little information which we can get another way. I will follow up the links you supplied. In the meantime thanks again. JMCS "'69 Camaro" wrote: Hi. The only time I've seen this happen with a query is when the table's index was corrupted. However, Jet will apparently choke on a Yes/No field in a GROUP BY query under special circumstances. (From your description, it sounds like there's only one column in your query, an aggregate function on a numerical data type, so this probably doesn't apply to you but it's something for future reference.) For more information on this, please see the following Web page: http://groups.google.com/group/micro...39994fe0c3a15c Have you tried a compact/repair recently? It's worth a try. If that doesn't help, try importing the tables and this query into a new database, remove the indexes, compact/repair, add the indexes again, and then run the query to see if you receive the same error in the new database. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips. (Please remove ZERO_SPAM from my reply E-mail address so that a message will be forwarded to me.) - - - If my answer has helped you, please sign in and answer yes to the question "Did this post answer your question?" at the bottom of the message, which adds your question and the answers to the database of answers. Remember that questions answered the quickest are often from those who have a history of rewarding the contributors who have taken the time to answer questions correctly. "JMCS" wrote: We have a query that has been working quite happily for over ayear, but has suddenly decided to play up, giving a "No current record" message box. The query is a Group By type, with just one of the items (hours) being "Sum". If we remove the Group By, the query works fine. We suspect a data problem, but cannot pinpoint it. Null is permitted in the hours field, and has been there in the data in the past. Any clues, please? JMCS |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Ambiguous outer joins | Renwick | New Users | 1 | February 22nd, 2005 02:08 PM |
O.E. Address Book Groups | Joy | Outlook Express | 12 | February 2nd, 2005 04:13 AM |
Mail Merge Problem | MT DOJ Help Desk | General Discussion | 9 | July 20th, 2004 04:55 AM |
Help Needed for Groups Please | Paul Black | General Discussion | 15 | June 21st, 2004 02:54 AM |
Product ID For Online Support | General Discussions | 12 | June 7th, 2004 07:34 PM |