CODEWITHSUNDEEP
ms-access
John G
John G

Reputation: 19

Ms Access need to make a calculation based on multiple criteria on same table field

i would like to ask the following question about a simple MS Access project,

I have a table with 4 fields A,B,C,SUM i would like to create a form to get the values A,B,C (done that) and a query to calculate a result based on multiple criteria of the table fields:

IF A<=5 AND B<=2 THEN SUM = X1 value  + Y1 value + (C * Z1 value) + H
IF A>=5,01 AND A<50 AND B<=2 THEN SUM = X2 value  + Y2 value + (C * Z1 value)+ H
IF A>50 AND B<=2 THEN SUM = X3 value  + Y3 value + (C * Z1 value)+ H

(X1, X2, X3, Y1, Y2, Y3, Z1, Z2, H are integer values that i already have)

IF A<=5 AND B>2 THEN SUM = X1 value  + Y1 value + (C * Z1 value)+ H
IF A>=5,01 AND A<50 AND B>2 THEN SUM = X2 value  + Y2 value + (C * Z1 value)+ H
IF A>50 AND B>2 THEN SUM = X3 value  + Y3 value + (C * Z1 value)+ H

Can someone help me on how to achieve this formula, I guess i have to build a query, but can someome guide me on the syntax ?

Thank you all in advance. !!!

Upvotes: 0

Views: 334

Answers (1)

Erick
Erick

Reputation: 314

To begin with, I recommend you not using 'SUM' as a field name since it represents an aggregate function and I belive also a reserved word. check(https://support.microsoft.com/en-us/office/learn-about-access-reserved-words-and-symbols-ae9d9ada-3255-4b12-91a9-f855bdd9c5a2#:~:text=%22Reserved%20words%22%20are%20words%20and,when%20referring%20to%20the%20field.) In MS Access SQL you may use the conditional statement IIF such as

IIF(CONDITION, return if true,return if false). Eventually you may nest IIF statements. Example

res(Your SUM) =(IIF(A<=5 AND B<=2, X1 + Y1 + H + C*Z1,
    IIF(A BETWEEN 5 AND 50 AND B2 >2,X2 + Y2 + H + C*Z1,X3 + Y3 + H + C*Z1))

So your query should be something like

 "SELECT A, B, C, (IIF(A<=5 AND B<=2, X1 + Y1 + H + C*Z1,
        IIF(A BETWEEN 5 AND 50 AND B2 >2,X2 + Y2 + H + C*Z1,X3 + Y3 + H + C*Z1)) AS res
 FROM your_table_name"

You will have to provide the Xs, Ys, Zs, H and C values either as parameters or hard typed. You may also look into using BETWEEN.

It may became kind of messy but this is it.

Upvotes: 0

Related Questions