Sorting based on an expression

Hi,

I have a report set up where I've set the grouping and sorting to:

Team Name
Title
Full Name

Basically, sorts the report by team, then puts people with the same titles 
together in alphabetical order.  I would like to now keep the same idea, but 
now sort the people with the same titles in descending order based on the # 
of hours they've worked.

I only show the footer of "Full Name" because I have it sum up data for a 
period of time and I don't want it to show the individual monthly data.  So 
the value I want to sort is an expression called [Text152] -- it's calculated 
when the report is opened.  How do I sort based on an expression that is 
calculated when the report is opened?

Thanks!!
Heather
0
Utf
12/11/2007 6:40:03 PM
access.reports 4434 articles. 0 followers. Follow

3 Replies
541 Views

Similar Articles

[PageSpeed] 37

You can't sort a report based on a value in a control. You can sort a report 
based on a value that can be calculated from a single record in the report's 
record source. 

You should be able to create a totals query tha calculates the "sort" 
expression. Then add this query to the report's record source query joining 
the appropriate fields. This allows you to use the calculated value in the 
sorting and grouping.

-- 
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP 
http://www.access.hookom.net/UCP/Default.htm


"HeatherD25" wrote:

> Hi,
> 
> I have a report set up where I've set the grouping and sorting to:
> 
> Team Name
> Title
> Full Name
> 
> Basically, sorts the report by team, then puts people with the same titles 
> together in alphabetical order.  I would like to now keep the same idea, but 
> now sort the people with the same titles in descending order based on the # 
> of hours they've worked.
> 
> I only show the footer of "Full Name" because I have it sum up data for a 
> period of time and I don't want it to show the individual monthly data.  So 
> the value I want to sort is an expression called [Text152] -- it's calculated 
> when the report is opened.  How do I sort based on an expression that is 
> calculated when the report is opened?
> 
> Thanks!!
> Heather
0
Utf
12/11/2007 7:41:00 PM
At the risk of asking a dumb question, how do I use two different queries in 
one report?  I know that I can generally combine two queries into one query, 
but I don't know how I would do that when one query is details and the other 
query is a sum of those details.  Any ideas for me? 

Thanks again!!!

"Duane Hookom" wrote:

> You can't sort a report based on a value in a control. You can sort a report 
> based on a value that can be calculated from a single record in the report's 
> record source. 
> 
> You should be able to create a totals query tha calculates the "sort" 
> expression. Then add this query to the report's record source query joining 
> the appropriate fields. This allows you to use the calculated value in the 
> sorting and grouping.
> 
> -- 
> Duane Hookom
> Microsoft Access MVP
> If I have helped you, please help me by donating to UCP 
> http://www.access.hookom.net/UCP/Default.htm
> 
> 
> "HeatherD25" wrote:
> 
> > Hi,
> > 
> > I have a report set up where I've set the grouping and sorting to:
> > 
> > Team Name
> > Title
> > Full Name
> > 
> > Basically, sorts the report by team, then puts people with the same titles 
> > together in alphabetical order.  I would like to now keep the same idea, but 
> > now sort the people with the same titles in descending order based on the # 
> > of hours they've worked.
> > 
> > I only show the footer of "Full Name" because I have it sum up data for a 
> > period of time and I don't want it to show the individual monthly data.  So 
> > the value I want to sort is an expression called [Text152] -- it's calculated 
> > when the report is opened.  How do I sort based on an expression that is 
> > calculated when the report is opened?
> > 
> > Thanks!!
> > Heather
0
Utf
12/14/2007 3:19:00 PM
"how I would do that when one query is details and the other query is a sum 
of those details"

Your summary query would be grouped by some field or fields that are also in 
your report's query. Just add the totals query in with your report's query 
and join these fields. This shouldn't increase the total number of records 
returned by the query if done correctly.

-- 
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP 
http://www.access.hookom.net/UCP/Default.htm


"HeatherD25" wrote:

> At the risk of asking a dumb question, how do I use two different queries in 
> one report?  I know that I can generally combine two queries into one query, 
> but I don't know how I would do that when one query is details and the other 
> query is a sum of those details.  Any ideas for me? 
> 
> Thanks again!!!
> 
> "Duane Hookom" wrote:
> 
> > You can't sort a report based on a value in a control. You can sort a report 
> > based on a value that can be calculated from a single record in the report's 
> > record source. 
> > 
> > You should be able to create a totals query tha calculates the "sort" 
> > expression. Then add this query to the report's record source query joining 
> > the appropriate fields. This allows you to use the calculated value in the 
> > sorting and grouping.
> > 
> > -- 
> > Duane Hookom
> > Microsoft Access MVP
> > If I have helped you, please help me by donating to UCP 
> > http://www.access.hookom.net/UCP/Default.htm
> > 
> > 
> > "HeatherD25" wrote:
> > 
> > > Hi,
> > > 
> > > I have a report set up where I've set the grouping and sorting to:
> > > 
> > > Team Name
> > > Title
> > > Full Name
> > > 
> > > Basically, sorts the report by team, then puts people with the same titles 
> > > together in alphabetical order.  I would like to now keep the same idea, but 
> > > now sort the people with the same titles in descending order based on the # 
> > > of hours they've worked.
> > > 
> > > I only show the footer of "Full Name" because I have it sum up data for a 
> > > period of time and I don't want it to show the individual monthly data.  So 
> > > the value I want to sort is an expression called [Text152] -- it's calculated 
> > > when the report is opened.  How do I sort based on an expression that is 
> > > calculated when the report is opened?
> > > 
> > > Thanks!!
> > > Heather
0
Utf
12/14/2007 4:46:01 PM
Reply:

Similar Artilces:

Subject based filtering of DSN messages
I can't make this work for the vast majority of the DSN messages we get (message undeliverable and such). I have setup rules to filter the different bounce messages that I see in subject lines and some of them work fine. Most do not. The messages the come from postfix (for instance) have mime-encoded notification messages, split in several parts. I've tried matching the Subject: line I end up seeing in Outlook and the subject line that is in the internet headers of the message, which is not what outlook shows me. I think exchange 2003 is recognizing these messages and try...

Sort Order on Order Fulfillment Window
In the sales order fulfillment window you can sort the lines items by Order Entered or Site/Bin/Item. Does the Site/Bin/Item only sort by Bin if Multi-bin is used? I am not using multi-bin but have created bin for each item in each site on the Quantities/Sites window. thanks. ...

View pareto chart by sorting the max number on the bottom
Is it possible to format the pareto chart by sorting the maximun number show in the bottom of each bar and ignore sort by Legend item? The order is determined by the Row Source of the chart. -- Duane Hookom Microsoft Access MVP "sq75222" wrote: > Is it possible to format the pareto chart by sorting the maximun number show > in the bottom of each bar and ignore sort by Legend item? > > ...

Sales order processing item inquiry windows
Hi, Since I have updated to version 10 (from version 9.0) the document are not showing in any specific order. In version 9 they were sorted by document date. For example if you choose "By item number" then include only invoice and then choose history. You will see that the document are not sorted at all. Any ideas or suggestions? Thanks Andre, Everything scrolling window has a sort order - just maybe not what you expect. But you can always see it by turning on your dexsql.log and seeing the query generated. In this case with your same options marked, i get: SELECT TOP...

Outlook Express 6 #54
When I am in Newsgroups and havee aa lot of items and want to close them, OE will ofton "hang" when I click "mark aall read". This throws me out of Outlook and onto my desktop. Using Media Center Edition 2005 XP. Does anyone else experience this? TIA Donald Sessler <sloopokespam@opto.net> wrote: > When I am in Newsgroups and havee aa lot of items and want to close > them, OE will ofton "hang" when I click "mark aall read". This throws > me out of Outlook and onto my desktop. Using Media Center Edition > 2005 XP. Does anyone else...

Outlook Express BCC options
Is there anyway to set Outlook Express to send email to multiple addresses ONLY BCC? I want to completly eliminate the option to CC. Thank you in advance! Christine ...

Adding functionality to MS Outlook Express
Hi, I am looking for help to integrate anti-spam open source programs with MS Outlook Express :- - SpamPal - http://www.spampal.org/ - Spamato - http://www.spamato.net/ The problem is to provide a library that will facilitate this. I have read the MSDN Messaging and Collaboration documentation which gives the ability to get notifications of new messages and to access message folders :- http://msdn2.microsoft.com/en-gb/library/ms709546.aspx The next need is to add a toolbar (with buttons for SPAM/NOSPAM) and to detect what messages are selected in the message window. The MSOE...

Sort House Numbers
How can I get excel to sort house numbers as I would like, i.e 1,2,2a,3,4,5,5a,5b, et. etc. So far I've ailed miserably. Any help out there? TIA Mike G Hi Mike, You will have to separate the digits into a separate cell. Extraction of a Group of Digits and Dashes, from postings by Harlan Grove Extract the First Set of Digits (#DigitsFirstID) http://www.mvps.org/dmcritchie/excel/grove_digitsid.htm#DigitsFirstID -- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvp...

Sorting Stacked Columns
I am trying to sort the columns so that the largest value is on th bottom each year. This would change the order of the series over eac year presumably. I have not figured out a way to do this, and fear i may be VBA related. Any ideas? Thanks. I attached the file for viewing Attachment filename: testbook_stackedcols.xls Download attachment: http://www.excelforum.com/attachment.php?postid=61644 -- Message posted from http://www.ExcelForum.com ...

Insert Blank Line and Sort
------------liX6Lxsnq7Ovs6LAASpU5y Content-Type: text/plain; charset=iso-8859-15; format=flowed; delsp=yes Content-Transfer-Encoding: 7bit Within a worksheet I have an example of data below consisting of four columns. I need to put a blank line in between the Racecourse (where they change) and after do a sort on Rating (high to low). Is it possible? I could do it with a macro, but am unable to fathom out how to deal with a differing number of horses at each racecourse. Time Racecourse Horse Rating 04:30:00 SANDOWN Dare To Dance 0.261 04:30:00 SANDOWN Garud 0.153 04:30:00 SANDOWN Wes...

Sorting Issue (Mixde Fields)
I have an issue where I need to sort a worksheet based on a field (Bin Location)that is sorting as seen below... 1A1 1A20 1A21 1A22 1A3 1A4 How can I get Excel to sort it the following way... 1A1 1A3 1A4 1A20 1A21 1A22 Please help.... I need to get this figured out today. I appreciate any responses. Thanks, Jonathan G. One way: add a column showing the length of the column you want to sort, then sort using the new column then the "real" column. "Jonathan G." wrote: > I have an issue where I need to sort a worksheet based on a field (Bin > Location)that is s...

Large List Sorting
Hello, All! Have a problem. I'm developing an application which reading a large database file (DBF) and displaying it in the list control. Because of the size of the file (>15 MB, later it will be increased) I'm using a virtual list (owner data). But now I need to sort data in the list. AFAIK to perfirm this I should read the whole file. In this case my program starts several minutes!!! It doesn't have to be like this [(C) Pink Floyd]... Is there any methods to load a large amount of data as quickly as possible? Or I just should humble, 'cause sorting of large amount of d...

How to sort in Excel with headers link to all body parts
I have a table that have subheaders and each subheader with rows of items below them. I wanted to sort all subitems from all subheaders according to value (in one column); and have each subitem link to the subheader as label for that particular item. I guess what I need to do is link a cluster of items to it subheader; so when I sort each item will include the subheader as label to show what subheader it came from. ...

cell to have comma-delimited values based on text
I have a table called "220_reference" with a column name "Part Number" having a sample value of below: 4047122(All Dash no.), 4057222(All Dash no.), 4058222(All Dash no.), 4060122(All Dash no.) The entire value is in one cell representing the "Part Number" column(defined as general type so text I suppose). Simple enough. But what I need to do is take any number that has "(All Dash no.)" after it and search through a column in another table to retrieve any rows that have that number(text) in it. The other table name is "220" with ...

Sorting Notes
I use the Outlook Notes program quite a bit. But, there are two problems I have with it: 1. It sorts most recent to oldest -- is there any way to change the sort order to alphabetical? 2. I'd like the default "note" size to be a little larger? Is this possible? Thanks I can answer part of this one. To make the note a little larger - Tools, Options, Note Options, Size. Judy Freed <EHPorter> wrote in message news:414a4d0d$0$55835$a1866201@newsreader.visi.com... > I use the Outlook Notes program quite a bit. But, there are two problems I > have wit...

sum items in a table based on description
I am interested in summing items is a table based on their description. Is there a formula that will add items together from column based on the contents in another cell reference in the same row? I know a pivot table will do this with some restrictions in the table design. You can use sumif if there is onbly one condition Assume you want to sum values in column C where column B is "x" =SUMIF(B2:B500,"x",C2:C500) Regards, Peo Sjoblom "Hrider" <hrider@yahoo.com> wrote in message news:ejggYE$MFHA.3328@TK2MSFTNGP14.phx.gbl... >I am interested ...

Receive PO based on amount.
Greetings GPLings.. :) Is there any way to receive with invoice , a PO that contain service /non inventoried items based on the amount? Eg: PO amount $100000000 for ONE service item and i would want to receive and invoice only $ 200. The reason i used this huge amount as PO is that even if i used a decimal value to receive the PO as a percentage,it will still not be accurate as GP allows only 5 decimal places. In a simple form, i want to receive based on the amount rather than qty. Cheers.. The only way to do that is to enter the PO for a quantity of 100,000,000 at a unit...

Join based on next closest value (like Excel VLOOKUP)
Trying to do something similar to a VLOOKUP (Excel) in an Access 2003 query. I have the following tables: JOBS Job,Quantity A,96 B,256 C,300 D,4299 COSTS Quantity,Cost 0, $1000 100, $1200 200, $1500 300, $2000 400, $2500 500, $3000 I need a query that takes JOBS.Quantity, looks it up in COSTS.Quantity and find the cost for the NEXT LOWEST quantity. (Example: Job B has a quantity of 256 and the next LOWEST quantity from COSTS is 200 so Job B costs $1500.) The results should be as follows: JOBS.Job,COSTS.Cost A,$1000 B,$1500 C,$2000 D,$3000 This would be ...

outlook express 6 to outlook express 2000
how do I transfer my address book from one computer running outlook express 6 to my new computer running outlook express 2000. Thanks Bob There is no such Outlook Express version as 2000...did you mean Outlook 2000, or did you mean some other version of OE? -- Jocelyn Fiorello MVP - Outlook *** Replies sent to my e-mail address will probably not be answered -- please reply only to the newsgroup to preserve the message thread. *** "Bob" <brubaker@bendcable.com> wrote in message news:05b501c34a6e$da07fb40$a301280a@phx.gbl... > how do I transfer my address book from o...

Home based Job For Indian
Want the pleasure of life, don't worry, work 1-2hours/day and earn Rs. 10,000-15,000/month online from your home/cafe/office PC. For details write # ejob1189@rediffmail.com Please write the subject of mail as: "Job Inquiry" ...

Fast alternative to table based state transition matrix
I got this answer from comp.theory. It was completely obvious once it was explained. It is trivially simple to create a DFA based recognizer without a state transition matrix data table. Simply encode case statements corresponding to inputs within the case elements of a case statement corresponding to states. In at least some cases the (case within case) method might be faster depending upon whether or not memory is reduced enough to more than offset the higher case statement overhead to increase cache locality of reference. "Peter Olcott" <NoSpam@OCR4Screen.com...

How to change the default font size for outlook express?
I would like change default font size for outlook express? the default font size is 10, and I prefer 12 as default. Does anyone have any suggestions? Thanks in advance for any suggestions Eric Tools | Options | Compose. -- Bruce Hagen MS-MVP [Mail] Imperial Beach, CA "Eric" <Eric@discussions.microsoft.com> wrote in message news:975D75C0-1CA0-4325-A9D6-EC7510AFA86C@microsoft.com... >I would like change default font size for outlook express? the default >font > size is 10, and I prefer 12 as default. > Does anyone hav...

Colouring Emails based on Headers
I am trying to use colour to pick out spam identified by a Spam program in an X-Header. I have gone into the advanced settings, but can't seem to find an option to colour based on content of the headers. Can anyone help me out here? SB Take a look at HeaderToCategory for MS Exchange 2000/2003 application: http://www.ivasoft.biz/hc.shtml It could help in that case. -- Rregards, Victor Ivanidze, software developer > I am trying to use colour to pick out spam identified by a Spam program in > an X-Header. > I have gone into the advanced settings, but can't seem to fin...

Payroll deduction based on percentage of another deduction
Is it possible to set up a payroll deduction based on percentage of another deduction? I see we can base a deduction on selected paycodes but not deductions. We can collect a percentage of wage attachments as collection fees but if the wage attachment deduction amount changes each pay, we'd have to manually change to collection fee code each pay. I'm looking for a way to calculate this collection fee automatically. ...

Row highlight based on the 1-31 day of the month
I've got a table with dates displayed in the first column (ascending order). I would like to highlight each row based on the day of the month with one of 5 chosen colours (red, yellow, green, blue and orange). rows with the 1st of the month: red rows with the 2nd of the month: yellow ....... rows with the 6th of the month: red ....... Julian. You would use conditional formatting to display different colors based upon the day value. Your conditional formatting formula would look at the day value of the date. You will have multiple conditions for each row. Condition 1 Formula is: ...