473,372 Members | 1,312 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

Total on report blows sorting

Hello All,

I have a report based upon a query. I have added a control to report
footer that calcs the total cost of the inventory: =SUM([inv_cost]).

When this total calculation is NOT on the report, the report displays
as it should in ascending order by inventory number (inv_num).

1102
1103
1104
....
....
....
2300

When the total calculation IS on the report, the sort gets messed up.
It is still ascending but does not start with the lowest inv_num.
It'll start about 2/3 through the invetory with the lowest numbered
item appearing about 1 third of the way throught the report:

2052
2053
....
....
....
....
....
2300
1102
1103
1104
1105
....
....
2050
2051

Any thought on why this is happening?

Nov 13 '05 #1
5 1894
A report's Sort property takes precedence over the record source's sort
order. Sounds like you may have an unintended group and/or sort property in
the report. Select "View - Sorting and Grouping" to check and edit as
necessary.
-Ed
"Mike" <mm****@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
Hello All,

I have a report based upon a query. I have added a control to report
footer that calcs the total cost of the inventory: =SUM([inv_cost]).

When this total calculation is NOT on the report, the report displays
as it should in ascending order by inventory number (inv_num).

1102
1103
1104
...
...
...
2300

When the total calculation IS on the report, the sort gets messed up.
It is still ascending but does not start with the lowest inv_num.
It'll start about 2/3 through the invetory with the lowest numbered
item appearing about 1 third of the way throught the report:

2052
2053
...
...
...
...
...
2300
1102
1103
1104
1105
...
...
2050
2051

Any thought on why this is happening?

Nov 13 '05 #2
I verified there are no Grouping.Soirting properties set. As I said,
without the text box with =SUM([inv_cost]), the sort is fine, acsending
by inv_num. With the addition of the =SUM([inv_cost]) in the report
footer, sorting turns bad.

Thanks

-Mike M

Nov 13 '05 #3
On 22 Feb 2005 06:42:02 -0800, "Mike" <mm****@gmail.com> wrote:
Hello All,

I have a report based upon a query. I have added a control to report
footer that calcs the total cost of the inventory: =SUM([inv_cost]).

When this total calculation is NOT on the report, the report displays
as it should in ascending order by inventory number (inv_num).

1102
1103
1104
...
...
...
2300

When the total calculation IS on the report, the sort gets messed up.
It is still ascending but does not start with the lowest inv_num.
It'll start about 2/3 through the invetory with the lowest numbered
item appearing about 1 third of the way throught the report:

2052
2053
...
...
...
...
...
2300
1102
1103
1104
1105
...
...
2050
2051

Any thought on why this is happening?


Are you expecting the results to be sorted using an ORDER BY in the underlying
query? That won't work on a report that has groups or sums. If your report
has any grouping at all or has an aggregate in the report footer, you must use
the report Sorting and Grouping to control the order of output.
Nov 13 '05 #4
Thanks for the explanation. I'll try it right now.

Thanks a lot

Nov 13 '05 #5
"Mike" wrote
I verified there are no Grouping.Soirting
properties set.
The Report's Sorting and Grouping are the ONLY thing that determine the
order in which information is displayed in a Report. The order of
information in the RecordSource is immaterial.
As I said, without the text
box with =SUM([inv_cost]), the sort is fine,
acsending by inv_num. With the addition
of the =SUM([inv_cost]) in the report
footer, sorting turns bad.


It was just happenstance that you got the order you wanted (it has to be
displayed in some order, and that was "convenient" for Access, but the
documentation says you must use Sorting and Grouping), and, as you
demonstrated, most anything can interfere with that order.

Try using the Report's Sorting and Grouping to specify sorting by the field
you want and see if that does not fix your problem.

Larry Linson
Microsoft Access MVP

Nov 13 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Richard Holliingsworth | last post by:
Hello: Thanks for reading this. I would like to build a report (Access 2002) that uses SQL queries to count records in specific groups. I've tried several methods and so far no luck. ...
6
by: Mike MacSween | last post by:
I have a report based on: PARAMETERS Forms!frmProductions!ProdID Long; SELECT Sum(qryRoleEvent.Fee) AS SumOfFee, First(qryMusician.LastName) AS LastName, First(qryMusician.FirstName) AS...
3
by: ahaque38 | last post by:
Hello. Using A2K SP3, I am having the following problem with a report using "Sorting and Grouping". I have recently added a grouping in the reports for "Category2<>'CONTRACTS'". I have...
11
by: James P. | last post by:
Hello, I have a report with the Priority field is used as sort order and grouping. The problem is the data in this Priority field if sorted in ascending order is: High, Low, and Medium. How...
8
by: Mike MacSween | last post by:
tblCourses one to many to tblEvents. A course may have an intro workshop (a type of event), a mid course workshop, a final exam. Or any combination. Or something different in the future. At...
7
by: manning_news | last post by:
I've got a report that's not sorting correctly. I build a SQL statement and assign it to the recordsource in the Open event, sorting the data the way the user chooses. The user can choose up to 3...
4
by: ApexData | last post by:
I have one table used to maintain information on Service Events. Each record contains a Repair, Maint, and Battery checkbox (bound/boolean). Any one of these fields may be checked or left empty....
4
by: Jana | last post by:
Hi! I have an Access 97 report based on tables linked to an SQL back end. The report has 5 subreports in it (unfortunately unavoidable due to the nature of the report) and performance is quite...
8
by: sara | last post by:
Hi - I have looked at all posts and tried both Allen Browne's Report Sorting at run Time ( Select Case Forms!frmChooseSort!grpSort Case 1 'Name Me.GroupLevel(0).ControlSource = "LastName"...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.