Query used for Report

[Linda]

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

 

[Marshall Barton]

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.

 

[Linda]

I did as you suggested but I see no difference. Any other things to try?

 

[Marshall Barton]

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)?

 

[Linda]

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.

 

[Mark Grizzle]

Use a case argument or iif() or switch() to return a number value for each
value.

Iif(field = "high",1,iif(field="medium",2,...

 

[Marshall Barton]

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.

 

[Linda]

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!