Assigning values once data filtered

[Guest]

Hi, hopefully someone can help me wit this:

I wish to assign an ordinal value to filtered data. For example, if I have
a field called "Windows version" and it includes data in the form of "95",
"98", "2000", or "XP".

Now I wish to create a new field called "windows version_num" with data
values as such "1", "2", "3", "4" representing windows 95 to XP etc. How
do I go about doing this.

In other words, this is what I start with...

Windows version
95
2000
98
2000
XP
etc.

And I want to end up with the following:

Windows version Windows version_num
95 1
2000 3
98 2
2000 3
XP 4

Any help would be greatly appreciated.

Thank you.

 

[Douglas J. Steele]

My advise would be to create a new table that contains one row for each
version of Windows you have. You can create this table from your existing
data using a DISTINCT query: create a query that returns only the Windows
version from the existing table, then go into the properties of the query
and set its Unique Values property to Yes. Once you know that the query is
working, convert it into a MAKE TABLE query (you can do this under the Query
menu, or there's a drop-down on the button bar)

Once you've saved the new table, go into it, add a new field to hold the
numeric value and assign a value to each row.

Then, you can join this new table to your existing table, and you'll be able
to display the number you just assigned in the new table as part of your
query.