A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

"Group By" problem



 
 
Thread Tools Display Modes
  #1  
Old October 5th, 2005, 03:20 PM
JMCS
external usenet poster
 
Posts: n/a
Default "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
Ads
  #2  
Old October 5th, 2005, 04:20 PM
'69 Camaro
external usenet poster
 
Posts: n/a
Default

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  
Old October 6th, 2005, 07:49 AM
JMCS
external usenet poster
 
Posts: n/a
Default

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  
Old October 7th, 2005, 04:29 PM
'69 Camaro
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Ambiguous outer joins Renwick New Users 1 February 22nd, 2005 03:08 PM
O.E. Address Book Groups Joy Outlook Express 12 February 2nd, 2005 05: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


All times are GMT +1. The time now is 05:23 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.