MULTIPLE CHANGES TO ONE FIELD

[Guest]

Hope you can help.

I am trying to do an update query on grouping for example field A as data
1,2,3,4,5,6,7,8,9,10. and in my new field =, field B I want to group this
for example "1 to 3", "4 to 6" ect. I am currently doing an update query for
each one group is there a way to combine all the querys into on rather than
loads?

hope this makes sence
many thanks
Steve

 

[Guest]

You can use IIF

I *think* this will work.

expr1: IIF ([fieldA]<4,1,IIF(([fieldA]>3) or ([fieldA]<7),2,
IIF([fieldA>6,3,99)))

 

[Guest]

The other method would be to have a one-to-many relationship to FieldA from
another table. This table has three categories

1 1,2,3
2 4,5,6
3, 7,8,9,10

In the query you can include this top table and then you won't have to worry
about the IIF statement.

--

http://www.ready4mainstream.ny911truth.org/index.html

You can use IIF

I *think* this will work.

expr1: IIF ([fieldA]<4,1,IIF(([fieldA]>3) or ([fieldA]<7),2,
IIF([fieldA>6,3,99)))

--

http://www.ready4mainstream.ny911truth.org/index.html

Hope you can help.

I am trying to do an update query on grouping for example field A as data
1,2,3,4,5,6,7,8,9,10. and in my new field =, field B I want to group this
for example "1 to 3", "4 to 6" ect. I am currently doing an update query for
each one group is there a way to combine all the querys into on rather than
loads?

hope this makes sence
many thanks
Steve

 

[John W. Vinson]

Hope you can help.

I am trying to do an update query on grouping for example field A as data
1,2,3,4,5,6,7,8,9,10. and in my new field =, field B I want to group this
for example "1 to 3", "4 to 6" ect. I am currently doing an update query for
each one group is there a way to combine all the querys into on rather than
loads?

You can do this with an additional table and a "Non Equi Join". Create a table
named Ranges with three fields: Low, High, and Range:
Low High Range
1 3 "1 to 3"
4 6 "4 to 6"

and so on.

Now create a query

SELECT yourtable.A, yourtable.this, yourtable.that, Ranges.Range AS B
FROM yourtable
INNER JOIN Ranges
ON yourtable.A >= Ranges.Low AND yourtable.A < Ranges.High;

It's neither necessary nor appropriate to store this range text in your table
or any other table, especially if the range boundaries might change, and even
MORE especially if the value of A might change (invalidating the resulting
range!)

John W. Vinson [MVP]