Calculated Fields with Conditionals

# Thread: Calculated Fields with Conditionals

1. Junior Member
Join Date
Dec 1969
Posts
22

## Calculated Fields with Conditionals

I&#039;m not sure if this is at all possible, but let me explain what I&#039;d like to do and find out if it can be done. I&#039;m using Access 2000. I have three fields; "Networth" and "Land" are number fields, and "Government" is a text field. I want to create a fourth calculated field in a query, called "NLG" which is a calculation based on the values of these three other fields. <BR><BR>NLG = Networth/Land * Government<BR><BR>The number constant for government depends on the value of the field. For example, if the Government field is "Republic" or "Democracy", then the value of the constant needs to be 1. If it is "Dictatorship" then it needs to be 0.85<BR><BR>So, is there any way to program all these "conditionals" into Access somewhere so it can do this automatically? Thanks for the help :-)

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## Sure...

If the only possible values for the Government field are the ones you mentioned, then it&#039;s pretty easy:<BR><BR>NLG = Networth / Land * IIF( Government=&#039;Dictatorship&#039;, 0.85, 1.0 )<BR><BR>Look up the docs for IIF. It&#039;s not as flexible as CASE WHEN, as supported by SQL Server, but it&#039;s usable.<BR><BR>To extend that a bit further, let&#039;s say you wanted to use these values:<BR>&nbsp; &nbsp; Democracy: 1.00<BR>&nbsp; &nbsp; Republic: 0.95<BR>&nbsp; &nbsp; Dictatorship: 0.85<BR><BR>You might code something like:<BR><BR>&nbsp; &nbsp; IIF( Government=&#039;Dictatorship&#039;, 0.85, IIF( Government=&#039;Republic&#039;, 0.95, 1.00 ) )<BR><BR>Make sense?<BR><BR>***************<BR><BR>But if you had many different types for Government, then the best thing might be to simply build a lookup table:<BR><BR>TABLE: GovType<BR>govtName : Text<BR>govtValue : Double<BR><BR>And then you do something like:<BR><BR>SELECT ..., GovType.govtValue AS NLG, ...<BR>FROM maintable, GovType<BR>WHERE GovType.govtName = maintable.Government<BR>...<BR><BR>SO there are many possibilities.<BR><BR>

3. Junior Member
Join Date
Dec 1969
Posts
22

## Perfect

Something like the IFF statement that I could place directly into the query field definition was exactly what I was looking for. Thanks as always, Bill :-)

4. Junior Member
Join Date
Dec 1969
Posts
5

## RE: Calculated Fields with Conditionals

If you only have the three types of Goverments,<BR>you can use an Imediate If statement "IIF" as shown below:<BR><BR>With a table "MyTable"<BR>and the three fields you requested<BR><BR>SELECT MyTable.Networth, MyTable.land, MyTable.Goverment, <BR><BR>(IIf([Goverment]="Dictatorship",0.85,1))*([Networth]/[land]) AS NLG<BR>FROM MyTable;<BR><BR><BR>If you have more than three types of Governments,<BR>I would suggest creating another Table as shown below:<BR><BR>MyRates table with two fields "Government" as text and "Rate" as long<BR><BR>Then use the SQL below:<BR><BR>SELECT MyTable.Networth, MyTable.land, MyRates.GovRate, [Networth]/[Land]*[GovRate] AS NLG<BR>FROM MyRates INNER JOIN MyTable ON MyRates.GovernmentID = MyTable.Goverment

5. Senior Member
Join Date
Dec 1969
Posts
96,118

## I really don't mind plagiarism...

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•