Sorting issue

[FA]

Hi, is there a way to arrange a field according to values in other
field. Actually i have two fields, RiskLevel and FindgingNum(PK).
FindingNum is a varchar sample FindingNum can be DOC20060131-AT001.
User input findings in finding form and they get lets say four findings
with four different finding number like
DOC20060131-AT001, Low
DOC20060131-AT002, High
DOC20060131-AT003, Med
DOC20060131-AT004,High

In report i can sort the findings according to high, med, low and
secondary sort finding number. But the problem is user want to see
high, med, low as well as FindingNum is asscending order like
001,002,003,004. I need a function that can allow user to enter the
findings in any order and they change the sequeence automatically
according to high,med, low.

If its possible, please help me out.

Thanks alot

 

[Guest]

Build a translation table --
Low 1
Med 2
High 3
Use it joined in your query to your RiskLevel field. Output the number for
sorting.
When sorting in a query the left-most field in the design view grid is the
high order sort followed by the filds to the right.

 

[FA]

Thanks Karl, I have created a table RSK_LVL as following
RSK_LVL_ID RSK_LVL
1 High
2 Medium
3 Low

I keyed my FINDG table as following
FINDG_NO RSK_LVL_ID

Still i am unclear about how i am going to rearange the FindngNum so
that they comes as 001,002,003 and the RSK_LVL_ID should comes as 1,2,3
User input the first finding number as DOC20060116-AT001 and he input
RSK_LVL_ID for that finding lets say 1
User again enter the finding number as DOC20060116-AT002 and he input
RSK_LVL_ID for that finding lets say 2
Latter he decides that the RSK_LVL_ID for the first finding should be 3
instead of 1. Now we have a sequence where the
first finding number has the RSK_LVL low and the second finding number
has the RSK_LVL medium.
I need a method to change the second finding number to 1 since medium
should come first and the first finding number to 2 since
low should comes after medium.

Please help me out in this situation, i have tried many things, even
tried aadding a sort field but didnt help.

Thanks

 

[jahoobob via AccessMonster.com]

You should be doing your sorting in a query, form, or report. Tables a just
places to store data, the others are for manipulating data so you cannot
depend on a table to "present" your data in a particular manner. Forget
about what the table looks like, it doesn't matter.
In a query place this last
ORDER BY FINDG_NO, RSK_LVL_ID ;
and you will get
DOC20060116-AT001,1
DOC20060116-AT001,2
DOC20060116-AT001,3
DOC20060116-AT002,1
DOC20060116-AT002,2
no matter what order they were entered or if they were changed.