Sorting and Grouping not working

M

mscertified

I have a table that when I run a GROUP BY query on it, it only groups up to a
certain point, then it returns ungrouped data. This has me completely
bewildered. At
some point I removed the primary key from this table (for unrelated reasons)
and I believe that is what triggered the problem. I have already tried
recreating the table and copying in all the data. This fixed the problem for
existing records but all newly added records again will not group.
This is the query that feeds the report:

SELECT RecordDate, RecordType, sum(HowMany) AS Num
FROM tblAddress
WHERE RecordDate Between NZ([forms]![frmPrint]![StartDate],#1/1/2000#) And
NZ([forms]![frmPrint]![EndDate],#12/31/2999#)
GROUP BY RecordDate, RecordType;

If I remove the grouping here and rely on the report to sort/group, the same
thing happens, existing records group and newly added records don't group
(i.e. they are printed one detail record at a time).

Anyone got any clues before I go crazy?
 
D

Douglas J. Steele

You have no choice but to use the report's Sorting and Grouping dialog:
reports do not respect the order of the incoming query.

When you run the query you show by itself, does it work?
 
M

mscertified

The same thing happens if I run the query independently. It groups up to a
certain point and then stops grouping.
I don't know if its a clue but in the table the records do not appear in the
order added. If I create a duplicate table with a primary autonumber key and
copy all the records in to it the last added records appear starting at id=1.

Douglas J. Steele said:
You have no choice but to use the report's Sorting and Grouping dialog:
reports do not respect the order of the incoming query.

When you run the query you show by itself, does it work?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


mscertified said:
I have a table that when I run a GROUP BY query on it, it only groups up to
a
certain point, then it returns ungrouped data. This has me completely
bewildered. At
some point I removed the primary key from this table (for unrelated
reasons)
and I believe that is what triggered the problem. I have already tried
recreating the table and copying in all the data. This fixed the problem
for
existing records but all newly added records again will not group.
This is the query that feeds the report:

SELECT RecordDate, RecordType, sum(HowMany) AS Num
FROM tblAddress
WHERE RecordDate Between NZ([forms]![frmPrint]![StartDate],#1/1/2000#) And
NZ([forms]![frmPrint]![EndDate],#12/31/2999#)
GROUP BY RecordDate, RecordType;

If I remove the grouping here and rely on the report to sort/group, the
same
thing happens, existing records group and newly added records don't group
(i.e. they are printed one detail record at a time).

Anyone got any clues before I go crazy?
Click to expand...
Click to expand...
 
C

Chuck

I have a table that when I run a GROUP BY query on it, it only groups up to a
certain point, then it returns ungrouped data. This has me completely
bewildered. At
some point I removed the primary key from this table (for unrelated reasons)
and I believe that is what triggered the problem. I have already tried
recreating the table and copying in all the data. This fixed the problem for
existing records but all newly added records again will not group.
This is the query that feeds the report:

SELECT RecordDate, RecordType, sum(HowMany) AS Num
FROM tblAddress
WHERE RecordDate Between NZ([forms]![frmPrint]![StartDate],#1/1/2000#) And
NZ([forms]![frmPrint]![EndDate],#12/31/2999#)
GROUP BY RecordDate, RecordType;

If I remove the grouping here and rely on the report to sort/group, the same
thing happens, existing records group and newly added records don't group
(i.e. they are printed one detail record at a time).

Anyone got any clues before I go crazy?
Click to expand...

Make sure you don't have any leading spaces in the table fields.
You can trim, but I prefer cleaning up the table.

Chuck
 
K

KARL DEWEY

Post a sample of the data returned just before sort ends and some after that
point.
--
KARL DEWEY
Build a little - Test a little


mscertified said:
The same thing happens if I run the query independently. It groups up to a
certain point and then stops grouping.
I don't know if its a clue but in the table the records do not appear in the
order added. If I create a duplicate table with a primary autonumber key and
copy all the records in to it the last added records appear starting at id=1.

Douglas J. Steele said:
You have no choice but to use the report's Sorting and Grouping dialog:
reports do not respect the order of the incoming query.

When you run the query you show by itself, does it work?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


mscertified said:
I have a table that when I run a GROUP BY query on it, it only groups up to
a
certain point, then it returns ungrouped data. This has me completely
bewildered. At
some point I removed the primary key from this table (for unrelated
reasons)
and I believe that is what triggered the problem. I have already tried
recreating the table and copying in all the data. This fixed the problem
for
existing records but all newly added records again will not group.
This is the query that feeds the report:

SELECT RecordDate, RecordType, sum(HowMany) AS Num
FROM tblAddress
WHERE RecordDate Between NZ([forms]![frmPrint]![StartDate],#1/1/2000#) And
NZ([forms]![frmPrint]![EndDate],#12/31/2999#)
GROUP BY RecordDate, RecordType;

If I remove the grouping here and rely on the report to sort/group, the
same
thing happens, existing records group and newly added records don't group
(i.e. they are printed one detail record at a time).

Anyone got any clues before I go crazy?
Click to expand...
Click to expand...
Click to expand...
 
M

mscertified

Here you go:

RecordDate RecordType Num
07/30/2007 KE 65
07/30/2007 KS 43
07/30/2007 RS 2
07/30/2007 SE 31
07/30/2007 SS 3
07/30/2007 XE 1
07/31/2007 KE 144
07/31/2007 KS 34
07/31/2007 RE 3
07/31/2007 RS 1
07/31/2007 SE 102
07/31/2007 SS 3
08/01/2007 SE 1
08/01/2007 KE 1
08/01/2007 KS 2
08/01/2007 KE 1
08/01/2007 KE 1
08/01/2007 KE 1
08/01/2007 KS 1
08/01/2007 KE 1
08/01/2007 KE 1
08/01/2007 KE 1
08/01/2007 KS 1
08/01/2007 KE 1
08/01/2007 RE 1
08/01/2007 KS 1
08/01/2007 SE 1
08/01/2007 KS 1
08/01/2007 KE 1
08/01/2007 SE 1
08/01/2007 KS 1
08/01/2007 KE 1
08/01/2007 SE 2
08/01/2007 KS 1
08/01/2007 SE 1
08/01/2007 KS 1
08/01/2007 SE 1
08/01/2007 KS 1
08/01/2007 SE 1
08/01/2007 KE 1
08/01/2007 KE 1
08/01/2007 SE 1
08/01/2007 SE 1
08/01/2007 KE 1
08/01/2007 KE 1
08/01/2007 KS 1
08/01/2007 KS 1

You can see the sorting/grouping ends after the July dates


KARL DEWEY said:
Post a sample of the data returned just before sort ends and some after that
point.
--
KARL DEWEY
Build a little - Test a little


mscertified said:
The same thing happens if I run the query independently. It groups up to a
certain point and then stops grouping.
I don't know if its a clue but in the table the records do not appear in the
order added. If I create a duplicate table with a primary autonumber key and
copy all the records in to it the last added records appear starting at id=1.

Douglas J. Steele said:
You have no choice but to use the report's Sorting and Grouping dialog:
reports do not respect the order of the incoming query.

When you run the query you show by itself, does it work?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have a table that when I run a GROUP BY query on it, it only groups up to
a
certain point, then it returns ungrouped data. This has me completely
bewildered. At
some point I removed the primary key from this table (for unrelated
reasons)
and I believe that is what triggered the problem. I have already tried
recreating the table and copying in all the data. This fixed the problem
for
existing records but all newly added records again will not group.
This is the query that feeds the report:

SELECT RecordDate, RecordType, sum(HowMany) AS Num
FROM tblAddress
WHERE RecordDate Between NZ([forms]![frmPrint]![StartDate],#1/1/2000#) And
NZ([forms]![frmPrint]![EndDate],#12/31/2999#)
GROUP BY RecordDate, RecordType;

If I remove the grouping here and rely on the report to sort/group, the
same
thing happens, existing records group and newly added records don't group
(i.e. they are printed one detail record at a time).

Anyone got any clues before I go crazy?
Click to expand...
Click to expand...
Click to expand...
 
M

mscertified

OK, guys I solved it.....
The dates before August are date only, August on they are are date/time,
hence the failure to group.
Now I have to figure out how they got that way.
This data is accumulated in another table and then is appended to this table
so I need to trace it through.
Thanks for your efforts...

KARL DEWEY said:
Post a sample of the data returned just before sort ends and some after that
point.
--
KARL DEWEY
Build a little - Test a little


mscertified said:
The same thing happens if I run the query independently. It groups up to a
certain point and then stops grouping.
I don't know if its a clue but in the table the records do not appear in the
order added. If I create a duplicate table with a primary autonumber key and
copy all the records in to it the last added records appear starting at id=1.

Douglas J. Steele said:
You have no choice but to use the report's Sorting and Grouping dialog:
reports do not respect the order of the incoming query.

When you run the query you show by itself, does it work?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have a table that when I run a GROUP BY query on it, it only groups up to
a
certain point, then it returns ungrouped data. This has me completely
bewildered. At
some point I removed the primary key from this table (for unrelated
reasons)
and I believe that is what triggered the problem. I have already tried
recreating the table and copying in all the data. This fixed the problem
for
existing records but all newly added records again will not group.
This is the query that feeds the report:

SELECT RecordDate, RecordType, sum(HowMany) AS Num
FROM tblAddress
WHERE RecordDate Between NZ([forms]![frmPrint]![StartDate],#1/1/2000#) And
NZ([forms]![frmPrint]![EndDate],#12/31/2999#)
GROUP BY RecordDate, RecordType;

If I remove the grouping here and rely on the report to sort/group, the
same
thing happens, existing records group and newly added records don't group
(i.e. they are printed one detail record at a time).

Anyone got any clues before I go crazy?
Click to expand...
Click to expand...
Click to expand...
 
M

mscertified

Thanks, although that was not the answer, adding trim() around each reference
led me to detect that at some point the dates become date/time and hence wil
not group.

Chuck said:
I have a table that when I run a GROUP BY query on it, it only groups up to a
certain point, then it returns ungrouped data. This has me completely
bewildered. At
some point I removed the primary key from this table (for unrelated reasons)
and I believe that is what triggered the problem. I have already tried
recreating the table and copying in all the data. This fixed the problem for
existing records but all newly added records again will not group.
This is the query that feeds the report:

SELECT RecordDate, RecordType, sum(HowMany) AS Num
FROM tblAddress
WHERE RecordDate Between NZ([forms]![frmPrint]![StartDate],#1/1/2000#) And
NZ([forms]![frmPrint]![EndDate],#12/31/2999#)
GROUP BY RecordDate, RecordType;

If I remove the grouping here and rely on the report to sort/group, the same
thing happens, existing records group and newly added records don't group
(i.e. they are printed one detail record at a time).

Anyone got any clues before I go crazy?
Click to expand...

Make sure you don't have any leading spaces in the table fields.
You can trim, but I prefer cleaning up the table.

Chuck
Click to expand...
 
K

KARL DEWEY

Try this ---
SELECT CVDATE(Int([RecordDate])), RecordType, sum(HowMany) AS Num
FROM tblAddress
WHERE RecordDate Between NZ([forms]![frmPrint]![StartDate],#1/1/2000#) And
NZ([forms]![frmPrint]![EndDate],#12/31/2999#)
GROUP BY CVDATE(Int([RecordDate])), RecordType;

--
KARL DEWEY
Build a little - Test a little


mscertified said:
Here you go:

RecordDate RecordType Num
07/30/2007 KE 65
07/30/2007 KS 43
07/30/2007 RS 2
07/30/2007 SE 31
07/30/2007 SS 3
07/30/2007 XE 1
07/31/2007 KE 144
07/31/2007 KS 34
07/31/2007 RE 3
07/31/2007 RS 1
07/31/2007 SE 102
07/31/2007 SS 3
08/01/2007 SE 1
08/01/2007 KE 1
08/01/2007 KS 2
08/01/2007 KE 1
08/01/2007 KE 1
08/01/2007 KE 1
08/01/2007 KS 1
08/01/2007 KE 1
08/01/2007 KE 1
08/01/2007 KE 1
08/01/2007 KS 1
08/01/2007 KE 1
08/01/2007 RE 1
08/01/2007 KS 1
08/01/2007 SE 1
08/01/2007 KS 1
08/01/2007 KE 1
08/01/2007 SE 1
08/01/2007 KS 1
08/01/2007 KE 1
08/01/2007 SE 2
08/01/2007 KS 1
08/01/2007 SE 1
08/01/2007 KS 1
08/01/2007 SE 1
08/01/2007 KS 1
08/01/2007 SE 1
08/01/2007 KE 1
08/01/2007 KE 1
08/01/2007 SE 1
08/01/2007 SE 1
08/01/2007 KE 1
08/01/2007 KE 1
08/01/2007 KS 1
08/01/2007 KS 1

You can see the sorting/grouping ends after the July dates


KARL DEWEY said:
Post a sample of the data returned just before sort ends and some after that
point.
--
KARL DEWEY
Build a little - Test a little


mscertified said:
The same thing happens if I run the query independently. It groups up to a
certain point and then stops grouping.
I don't know if its a clue but in the table the records do not appear in the
order added. If I create a duplicate table with a primary autonumber key and
copy all the records in to it the last added records appear starting at id=1.

:

You have no choice but to use the report's Sorting and Grouping dialog:
reports do not respect the order of the incoming query.

When you run the query you show by itself, does it work?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have a table that when I run a GROUP BY query on it, it only groups up to
a
certain point, then it returns ungrouped data. This has me completely
bewildered. At
some point I removed the primary key from this table (for unrelated
reasons)
and I believe that is what triggered the problem. I have already tried
recreating the table and copying in all the data. This fixed the problem
for
existing records but all newly added records again will not group.
This is the query that feeds the report:

SELECT RecordDate, RecordType, sum(HowMany) AS Num
FROM tblAddress
WHERE RecordDate Between NZ([forms]![frmPrint]![StartDate],#1/1/2000#) And
NZ([forms]![frmPrint]![EndDate],#12/31/2999#)
GROUP BY RecordDate, RecordType;

If I remove the grouping here and rely on the report to sort/group, the
same
thing happens, existing records group and newly added records don't group
(i.e. they are printed one detail record at a time).

Anyone got any clues before I go crazy?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

GROUP BY weirdness 1
Trying to select data range for date/time column 2
Why does the grouping in this query not work? 4
Sorting and Grouping 2
Report - Sorting and Grouping 2
sorting and grouping issue 2
Grouping and Sorting a Report On Open Event 5
Sorting and Grouping 1

Top