Update Query with multiple criterias!

[kyle18]

Hi everyone,

I need help with one very simply update query which I need to run.
I have made a simple update query with one criteria:
It works great.

UPDATE tblArticle INNER JOIN qrsLimPriceNEW ON tblArticle.ArticleID = qrsLimPriceNEW.ArticleID SET tblArticle.PriceROZCoef = 4
WHERE (([qrsLimPriceNEW].[Expr2] Between 2.21 And 2.95));

Now my next step is to create query where i have mulltiple creteria for example:
UPDATE tblArticle INNER JOIN qrsLimPriceNEW ON tblArticle.ArticleID = qrsLimPriceNEW.ArticleID SETtblArticle.PriceROZCoef = 2 WHERE (([qrsLimPriceNEW].[Expr2] Between 0.61 And 1.50));
tblArticle.PriceROZCoef = 3 WHERE (([qrsLimPriceNEW].[Expr2] Between 1.51 And 2.2));
tblArticle.PriceROZCoef = 4 WHERE (([qrsLimPriceNEW].[Expr2] Between 2.21 And 2.95));
All together I have 7 different conditions.

I simply don't know what funcition I need to use to make it work in one query together not 7.

I tried searching there some similar problems but I still don't understand.
Thanks for help in advance.

 

[plog]

The function you need to use is a custom one. The best way to accomplish this is to build a table with your ranges like so:

RangeFloor, RangeCeiling, RangeID
.61, 1.5, 2
1.51, 2.2, 3
2.21, 2.95, 4

Then you build a function in a module that takes [qrsLimPriceNEW] as an argument and then uses a DLookup to determine which RangeID from this new table to return.

Why are you running this as an APPEND query? Why not just a SELECT?

 

[MSAccessRookie]

Hi everyone,

I need help with one very simply update query which I need to run.
I have made a simple update query with one criteria:
It works great.

UPDATE tblArticle INNER JOIN qrsLimPriceNEW ON tblArticle.ArticleID = qrsLimPriceNEW.ArticleID SET tblArticle.PriceROZCoef = 4
WHERE (([qrsLimPriceNEW].[Expr2] Between 2.21 And 2.95));

Now my next step is to create query where i have mulltiple creteria for example:
UPDATE tblArticle INNER JOIN qrsLimPriceNEW ON tblArticle.ArticleID = qrsLimPriceNEW.ArticleID SETtblArticle.PriceROZCoef = 2 WHERE (([qrsLimPriceNEW].[Expr2] Between 0.61 And 1.50));
tblArticle.PriceROZCoef = 3 WHERE (([qrsLimPriceNEW].[Expr2] Between 1.51 And 2.2));
tblArticle.PriceROZCoef = 4 WHERE (([qrsLimPriceNEW].[Expr2] Between 2.21 And 2.95));
All together I have 7 different conditions.

I simply don't know what funcition I need to use to make it work in one query together not 7.

I tried searching there some similar problems but I still don't understand.
Thanks for help in advance.

You can write a VBA Function to do this for you, or if you need/want to use SQL to do the job, try looking into the Switch() Function and see if it can get you where you want.

http://www.techonthenet.com/access/functions/advanced/switch.php

 

[kyle18]

The function you need to use is a custom one. The best way to accomplish this is to build a table with your ranges like so:

RangeFloor, RangeCeiling, RangeID
.61, 1.5, 2
1.51, 2.2, 3
2.21, 2.95, 4

Then you build a function in a module that takes [qrsLimPriceNEW] as an argument and then uses a DLookup to determine which RangeID from this new table to return.

Why are you running this as an APPEND query? Why not just a SELECT?

The reason why I am using Append query is because I am not really experience SQL and its functions.

How would I use SELECT?

 

[plog]

APPEND and SELECT aren't functions, they are types of queries. The basis of all queries is a SELECT query, which shows you (or another query or report) just the data you have designated and in the order you want it. APPEND (and MAKE TABLE and DELETE) queries are action queries, which means they actually make changes within your database.

SELECT queries are essentially dynamic and action queries capture data at one moment in time. Suppose [qrsLimPriceNEW] is .68 today, if you run an APPEND query today that record's [PriceROZCoef] becomes 2. Now suppose you find an error, or just through the process of updating data the [qrsLimPriceNew] field gets updated to 2.25. A SELECT query would automatically capture that and if you ran it today would correctly spit out that the record's [PriceROZCoef] is now 4. With an APPEND query you would either have to manually update that field, or delete the table that holds the APPEND data and rerun the APPEND query.