Query used for Report Linda 12/4/2008 9:39:03 PM I have created a query on a table to be used for a report. The sorting is not working like I would expect and I can't figure out why. I have 4 fields in the select query design: Risk_Exp_Lvl sorted Ascending Criteria "High" or "Medium" Risk_Exposure sorted Descending Prob_Occur Conseq_Occur Risk_Exp_Lvl Risk_exposure Prob_Occur Conseq_Occur High 25 5 5 High 25 5 5 High 25 5 5 High 16 4 4 High 16 4 4 High 15 3 5 High 15 5 3 High 15 5 3 Medium 9 3 3 Medium 9 3 3 Medium 8 2 4 Medium 8 2 4 Medium 8 4 2 Medium 6 2 3 Medium 5 1 5 Medium 12 3 4 Medium 12 4 3 Medium 12 4 3 Medium 12 3 4 Medium 10 2 5 Medium 10 5 2 It all looks like I would expect until near the end when the 2nd column value jumps to 12 and it looks like the sorting starts over. Does anyone know why this might be happening? I don't know where to look. Thanks, Linda -- Linda

Re: Query used for Report Marshall Barton <marshbarton[ at ]wowway.com> 12/4/2008 10:21:15 PM Linda wrote: [Quoted Text] >I have created a query on a table to be used for a report. The sorting is >not working like I would expect and I can't figure out why. > >I have 4 fields in the select query design: >Risk_Exp_Lvl sorted Ascending Criteria "High" or "Medium" >Risk_Exposure sorted Descending >Prob_Occur >Conseq_Occur > >Risk_Exp_Lvl Risk_exposure Prob_Occur Conseq_Occur >High 25 5 5 >High 25 5 5 >High 25 5 5 >High 16 4 4 >High 16 4 4 >High 15 3 5 >High 15 5 3 >High 15 5 3 >Medium 9 3 3 >Medium 9 3 3 >Medium 8 2 4 >Medium 8 2 4 >Medium 8 4 2 >Medium 6 2 3 >Medium 5 1 5 >Medium 12 3 4 >Medium 12 4 3 >Medium 12 4 3 >Medium 12 3 4 >Medium 10 2 5 >Medium 10 5 2 > >It all looks like I would expect until near the end when the 2nd column >value jumps to 12 and it looks like the sorting starts over. Does anyone >know why this might be happening? Remove the sorting from the query and sort the report using the Sorting and Grouping window (View menu) while the report is open in design view. -- Marsh MVP [MS Access]

Re: Query used for Report Linda 12/4/2008 11:31:01 PM I did as you suggested but I see no difference. Any other things to try? -- Linda "Marshall Barton" wrote: [Quoted Text] > Linda wrote: > > >I have created a query on a table to be used for a report. The sorting is > >not working like I would expect and I can't figure out why. > > > >I have 4 fields in the select query design: > >Risk_Exp_Lvl sorted Ascending Criteria "High" or "Medium" > >Risk_Exposure sorted Descending > >Prob_Occur > >Conseq_Occur > > > >Risk_Exp_Lvl Risk_exposure Prob_Occur Conseq_Occur > >High 25 5 5 > >High 25 5 5 > >High 25 5 5 > >High 16 4 4 > >High 16 4 4 > >High 15 3 5 > >High 15 5 3 > >High 15 5 3 > >Medium 9 3 3 > >Medium 9 3 3 > >Medium 8 2 4 > >Medium 8 2 4 > >Medium 8 4 2 > >Medium 6 2 3 > >Medium 5 1 5 > >Medium 12 3 4 > >Medium 12 4 3 > >Medium 12 4 3 > >Medium 12 3 4 > >Medium 10 2 5 > >Medium 10 5 2 > > > >It all looks like I would expect until near the end when the 2nd column > >value jumps to 12 and it looks like the sorting starts over. Does anyone > >know why this might be happening? > > > Remove the sorting from the query and sort the report using > the Sorting and Grouping window (View menu) while the report > is open in design view. > > -- > Marsh > MVP [MS Access] >

Re: Query used for Report Marshall Barton <marshbarton[ at ]wowway.com> 12/5/2008 12:36:17 AM Linda wrote: [Quoted Text] >I did as you suggested but I see no difference. Any other things to try? This is an extremely simple thing to do, so if you did things correctly in Sorting and Grouping, then it's time to look at precisly what you tried and the exact values in the fields. Is Risk_Exposure field a number type? Do any records have any other characters in the Risk_Exposure or Risk_Exp_Lvl field (e.g. leading/trailing spaces)? -- Marsh MVP [MS Access]

Re: Query used for Report Linda 12/9/2008 11:20:02 PM I did find that the risk exposure was defined as a string and I was making calculations with it as a number. Once I went through an repopulated the field with the correct type the sorting appears to work correctly. Thank you for that pointer! Now, my Risk_Exp_Lvl contains vaules like High, Medium, Low and None. How do I sort them in that order? Alphabetically doesn't do it. Thanks again. -- Linda "Marshall Barton" wrote: [Quoted Text] > Linda wrote: > > >I did as you suggested but I see no difference. Any other things to try? > > > This is an extremely simple thing to do, so if you did > things correctly in Sorting and Grouping, then it's time to > look at precisly what you tried and the exact values in the > fields. > > Is Risk_Exposure field a number type? Do any records have > any other characters in the Risk_Exposure or Risk_Exp_Lvl > field (e.g. leading/trailing spaces)? > > -- > Marsh > MVP [MS Access] >

Re: Query used for Report Mark Grizzle <mgrizzle[ at ]oeh.com> 12/10/2008 1:14:09 AM Use a case argument or iif() or switch() to return a number value for each value. Iif(field = "high",1,iif(field="medium",2,... On 12/9/08 6:20 PM, in article 567D656C-A954-4244-9D6D-E0953805AA2F[ at ]microsoft.com, "Linda" <Linda[ at ]discussions.microsoft.com> wrote: [Quoted Text] > I did find that the risk exposure was defined as a string and I was making > calculations with it as a number. Once I went through an repopulated the > field with the correct type the sorting appears to work correctly. Thank you > for that pointer! > > Now, my Risk_Exp_Lvl contains vaules like High, Medium, Low and None. How > do I sort them in that order? Alphabetically doesn't do it. > Thanks again.

Re: Query used for Report Marshall Barton <marshbarton[ at ]wowway.com> 12/10/2008 2:42:10 PM Linda wrote: [Quoted Text] > >I did find that the risk exposure was defined as a string and I was making >calculations with it as a number. Once I went through an repopulated the >field with the correct type the sorting appears to work correctly. Thank you >for that pointer! > >Now, my Risk_Exp_Lvl contains vaules like High, Medium, Low and None. How >do I sort them in that order? Alphabetically doesn't do it. The database way of doing that kind of sort is to create a simple little table with two fields: Table: RiskLevels Fields: Key Integer Descr Text Then populate it with records like: 1 High 2 Medium 3 Low 4 None This simple arrangement allows you to use a combo box on a form to select the risk level so users can not make up their own descriptions of risk and it also eliminates the chance of spelling errors. Your existing table should then be changed to use the RiskLevels table's Key field instead of your current Text field. I.e. the combo box's BoundColumn would be the Key field, but would display the Descr field. At this point the sorting you need is trivial. To display the text in a report, the report will need to use a query as its record source. The query would simply join the RiskLevels table to your existing table to pick up the Descriptive text. One major additional benefit of this seemingly "extra" effort is that you can change the descriptive text without changing anything in your queries, forms or reports. Adding additional risk levels would also be a code/form/report free change. -- Marsh MVP [MS Access]

Re: Query used for Report Linda 12/10/2008 11:08:10 PM That did it! Thank you so much for your help. Marshall, you do a great job of writing your recommendations clearly so even the novice can follow your advice. Keep up the good work! -- Linda "Marshall Barton" wrote: [Quoted Text] > Linda wrote: > > > > >I did find that the risk exposure was defined as a string and I was making > >calculations with it as a number. Once I went through an repopulated the > >field with the correct type the sorting appears to work correctly. Thank you > >for that pointer! > > > >Now, my Risk_Exp_Lvl contains vaules like High, Medium, Low and None. How > >do I sort them in that order? Alphabetically doesn't do it. > > > The database way of doing that kind of sort is to create a > simple little table with two fields: > > Table: RiskLevels > Fields: Key Integer > Descr Text > Then populate it with records like: > 1 High > 2 Medium > 3 Low > 4 None > > This simple arrangement allows you to use a combo box on a > form to select the risk level so users can not make up their > own descriptions of risk and it also eliminates the chance > of spelling errors. > > Your existing table should then be changed to use the > RiskLevels table's Key field instead of your current Text > field. I.e. the combo box's BoundColumn would be the Key > field, but would display the Descr field. At this point the > sorting you need is trivial. > > To display the text in a report, the report will need to use > a query as its record source. The query would simply join > the RiskLevels table to your existing table to pick up the > Descriptive text. > > One major additional benefit of this seemingly "extra" > effort is that you can change the descriptive text without > changing anything in your queries, forms or reports. Adding > additional risk levels would also be a code/form/report free > change. > > -- > Marsh > MVP [MS Access] >