36 categories how to update? Version: 2003 (11.0)

[lobhaan]

Goodmorning,

Got a problem here with Access 2003: got 36 categories (example: volume from 0-0.1=category 1 vol from 0.1-0.2=category 2 etc) and a whole bunch of records (in a table) stating volume per record (0.111, 0.23456 etc). For each record I need to know which category it is. Now in order to prevent me from writing 36 update queries for each category (if vol>0 and <0.1 then category = 1 etc) there must b a smarter/faster way to do this. What is it?

Thx,
Lobhaan

 

[Mile-O]

Is each category represented by a field? If so then you need to normalize your database structure as a repeating group such as that is an indication that you require a further table.

 

[mresann]

I'd go so far as to say that I don't even think he needs to add fields. If he has a consistant algorythym, then all he needs is to query one field with the formula to produce the result.

Let's say he has a field called "Volume" with the value in the field. A query would be created called Category that uses the query calculation. Using your pattern, where every tenth of a Volume amount indicates a Category increase of 1, use the following formula in a query:

Category: "Category " & Int([Volume]*10)+1

Now, in your example you weren't too exact. For instance, you wanted Category 1 for everything from 0 to 0.1, Category from everything from 0.1 to 0.2, etc. BUT you fail to be explicit for the value of 0.1 (and in extension, every other value of volume that calculates to an EXACT tenth of a unit).

If the value of 0.1 would fall under Category 2, then Category will suffice. However, if in your scenario the value of 0.1 falls under Category 1, then use the following formula instead.

Category1: "Category " & IIf([volume]*10=Int([volume]*10),Int([volume]*10),Int([volume]*10)+1)

Bottom line: Use queries to create the calculations and fields necessary to provide the data you wish to present, rather than the tables themselves.

 

[Pat Hartman]

Create a table with three columns - minvalue, maxvalue, category. Join to that table using a non-equi-join

From yourtable inner join othertable on SomeValue Between minvalue and maxvalue;

 

[lobhaan]

thx for the help

it has been of great help!