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 » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Sorting records by a calculated field.



 
 
Thread Tools Display Modes
  #1  
Old September 28th, 2004, 08:33 PM
external usenet poster
 
Posts: n/a
Default Sorting records by a calculated field.

Hello There,

Can anybody help me? I have a report and I am trying to
sort my records by a calculated field.

I would like to list the records on the report by listing
those records with the highest value first in descending
order.

The field is located in the Client ID Header section of
the report (clients are currently sorted by ID in
ascending order)

How can i get the report to sort the clients by highest
value in the calculated field in descending order instead
of client ID in ascending order?

Can anybody please help

Thanks
Adam
  #2  
Old September 28th, 2004, 09:53 PM
Rusty Shackelford
external usenet poster
 
Posts: n/a
Default

I need to know how to do this too.

wrote in message
...
Hello There,

Can anybody help me? I have a report and I am trying to
sort my records by a calculated field.

I would like to list the records on the report by listing
those records with the highest value first in descending
order.

The field is located in the Client ID Header section of
the report (clients are currently sorted by ID in
ascending order)

How can i get the report to sort the clients by highest
value in the calculated field in descending order instead
of client ID in ascending order?

Can anybody please help

Thanks
Adam



  #3  
Old September 28th, 2004, 10:19 PM
Marshall Barton
external usenet poster
 
Posts: n/a
Default

wrote:
Can anybody help me? I have a report and I am trying to
sort my records by a calculated field.

I would like to list the records on the report by listing
those records with the highest value first in descending
order.

The field is located in the Client ID Header section of
the report (clients are currently sorted by ID in
ascending order)

How can i get the report to sort the clients by highest
value in the calculated field in descending order instead
of client ID in ascending order?



It depends on the calculation. You can use an expression in
the sorting and grouping field/expression columns. E.g.

=Price * Quantity

The expression must refer to fields in the report's reord
source, it can not refer to controls on the report.

--
Marsh
MVP [MS Access]
  #4  
Old September 28th, 2004, 10:33 PM
Rusty Shackelford
external usenet poster
 
Posts: n/a
Default

what about totals?
I want a report say on customers sorted by total orders?
But what I realy want is a report that gives a percentage of errors with
subited paperwork ( I have the report I just cant sort on the percentage)
"Marshall Barton" wrote in message
news
wrote:
Can anybody help me? I have a report and I am trying to
sort my records by a calculated field.

I would like to list the records on the report by listing
those records with the highest value first in descending
order.

The field is located in the Client ID Header section of
the report (clients are currently sorted by ID in
ascending order)

How can i get the report to sort the clients by highest
value in the calculated field in descending order instead
of client ID in ascending order?



It depends on the calculation. You can use an expression in
the sorting and grouping field/expression columns. E.g.

=Price * Quantity

The expression must refer to fields in the report's reord
source, it can not refer to controls on the report.

--
Marsh
MVP [MS Access]



  #5  
Old September 29th, 2004, 06:04 AM
Allen Browne
external usenet poster
 
Posts: n/a
Default

Rusty, it makes sense that Access cannot sort the report by a total that it
has not calculated until it has finished making the report. If you wish to
sort by a total, then you must get the total into the report's RecordSource.

You may be able to use a subquery. However, that approach is often
frustrated after you get the query working, and the report complains about a
"multi-level group by". So the solution probably involved creating another
query that calculates the totals (grouped by whatever), and using that query
as an input "table" in the report's query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Rusty Shackelford" wrote in message
...
what about totals?
I want a report say on customers sorted by total orders?
But what I realy want is a report that gives a percentage of errors with
subited paperwork ( I have the report I just cant sort on the percentage)
"Marshall Barton" wrote in message
news
wrote:
Can anybody help me? I have a report and I am trying to
sort my records by a calculated field.

I would like to list the records on the report by listing
those records with the highest value first in descending
order.

The field is located in the Client ID Header section of
the report (clients are currently sorted by ID in
ascending order)

How can i get the report to sort the clients by highest
value in the calculated field in descending order instead
of client ID in ascending order?



It depends on the calculation. You can use an expression in
the sorting and grouping field/expression columns. E.g.

=Price * Quantity

The expression must refer to fields in the report's reord
source, it can not refer to controls on the report.

--
Marsh
MVP [MS Access]



  #6  
Old September 29th, 2004, 06:25 AM
Marshall Barton
external usenet poster
 
Posts: n/a
Default

Rusty Shackelford wrote:

what about totals?
I want a report say on customers sorted by total orders?


To sort on a calculation that involves multiple records, the
sort value must be a field in the report's record source
query. This is normally done using a Totals (Group By) type
query. I would need to understand the report record source,
how each detail record contributes to the total and where
you want to display the total before I can make specific
suggestions on how to construct the report's new query.


But what I realy want is a report that gives a percentage of errors with
subited paperwork ( I have the report I just cant sort on the percentage)


I don't have any idea how you identify a paperwork error
here. This calulation would probably also be done in the
same query as the above total.
--
Marsh
MVP [MS Access]



wrote:
Can anybody help me? I have a report and I am trying to
sort my records by a calculated field.

I would like to list the records on the report by listing
those records with the highest value first in descending
order.

The field is located in the Client ID Header section of
the report (clients are currently sorted by ID in
ascending order)

How can i get the report to sort the clients by highest
value in the calculated field in descending order instead
of client ID in ascending order?



"Marshall Barton" wrote
It depends on the calculation. You can use an expression in
the sorting and grouping field/expression columns. E.g.

=Price * Quantity

The expression must refer to fields in the report's reord
source, it can not refer to controls on the report.


  #7  
Old October 1st, 2004, 10:04 PM
Rob
external usenet poster
 
Posts: n/a
Default

This may sound dumb, but I use it all the time:
If you have the total you want to sort by in the
query...rename the query to QUERYNAME1. Open a new query
and base it off of QUERYNAME1, bring in all the fields and
sort as you need, then save it as the original query
name.

When you open the report, the 2nd query calls the 1st to
run. It runs, generates the variable field you need, then
the 2nd query grabs that data, sorts it, and then feeds it
to to the report.

-----Original Message-----
Hello There,

Can anybody help me? I have a report and I am trying to
sort my records by a calculated field.

I would like to list the records on the report by listing
those records with the highest value first in descending
order.

The field is located in the Client ID Header section of
the report (clients are currently sorted by ID in
ascending order)

How can i get the report to sort the clients by highest
value in the calculated field in descending order instead
of client ID in ascending order?

Can anybody please help

Thanks
Adam
.

 




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
sort records by size of items in memo field, or pull those > 255 s PitBullBlonde Running & Setting Up Queries 1 September 22nd, 2004 09:31 PM
Sorting a field John Database Design 1 September 22nd, 2004 03:38 AM
New records can't be seen rleblanc Using Forms 6 August 14th, 2004 02:43 PM
NUMBERING the pages Bob New Users 7 June 14th, 2004 12:20 AM


All times are GMT +1. The time now is 01:01 AM.


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