If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
If Statements (Nested??)
I am trying to accomplish the following in a Microsoft Access 2007 QUERY. I
want to evaluate a field called "Cost Type" for the following values and then populate a new field (E1) with a value based on the Cost Type. For example If Cost Type = L, populate E1 with 1405 If Cost Type = M, populate E1 with 1410 If Cost Type = S, populate E1 with 1480 If Cost Type = G, populate E1 with 1495 Then.....I want to evaluate a different field (Pay Item) for the following conditions and place a static value in the E1 field...and..these values a If Pay Item = 955000, populate E1 with 1490 If Pay Item = 955300, populate E1 with 1491 If Pay Item = 955200, populate E1 with 1492 If Pay Item = 955100, populate E1 with 1493 Is there any way to combine these conditions (evalutating 2 fields) into 1 statement Thank you Warren Phillips |
#2
|
|||
|
|||
If Statements (Nested??)
Which gets pripority? If both IIFs are satisfied then which do you put in E1?
-- Build a little, test a little. "Warren Phillips" wrote: I am trying to accomplish the following in a Microsoft Access 2007 QUERY. I want to evaluate a field called "Cost Type" for the following values and then populate a new field (E1) with a value based on the Cost Type. For example If Cost Type = L, populate E1 with 1405 If Cost Type = M, populate E1 with 1410 If Cost Type = S, populate E1 with 1480 If Cost Type = G, populate E1 with 1495 Then.....I want to evaluate a different field (Pay Item) for the following conditions and place a static value in the E1 field...and..these values a If Pay Item = 955000, populate E1 with 1490 If Pay Item = 955300, populate E1 with 1491 If Pay Item = 955200, populate E1 with 1492 If Pay Item = 955100, populate E1 with 1493 Is there any way to combine these conditions (evalutating 2 fields) into 1 statement Thank you Warren Phillips |
#3
|
|||
|
|||
If Statements (Nested??)
That will never happen. The values in both fields and the values I am
wanting to populate in the new E1 field will always be unique for any given record. There will be multiple records that have a Cost Type of L or a Cost Type of M, etc.....or.....a Pay Item of 955000 or a Pay Item of 955300......but each record will contain one or the other. In other words, there would never be a Cost Type of L AND a Pay Item of 955000 in the same record, etc..... Thank you Karl!!!! "KARL DEWEY" wrote: Which gets pripority? If both IIFs are satisfied then which do you put in E1? -- Build a little, test a little. "Warren Phillips" wrote: I am trying to accomplish the following in a Microsoft Access 2007 QUERY. I want to evaluate a field called "Cost Type" for the following values and then populate a new field (E1) with a value based on the Cost Type. For example If Cost Type = L, populate E1 with 1405 If Cost Type = M, populate E1 with 1410 If Cost Type = S, populate E1 with 1480 If Cost Type = G, populate E1 with 1495 Then.....I want to evaluate a different field (Pay Item) for the following conditions and place a static value in the E1 field...and..these values a If Pay Item = 955000, populate E1 with 1490 If Pay Item = 955300, populate E1 with 1491 If Pay Item = 955200, populate E1 with 1492 If Pay Item = 955100, populate E1 with 1493 Is there any way to combine these conditions (evalutating 2 fields) into 1 statement Thank you Warren Phillips |
#4
|
|||
|
|||
If Statements (Nested??)
Try this. Create a table --
tblConvert -- Cost Type - text Pay Item - number E1 - text Populate like this -- Cost Type Pay Item E1 L 1405 M 1410 S 1480 G 1495 955000 1490 955300 1491 955200 1492 955100 1493 SELECT YourTable.[Cost Type], YourTable.[Pay Item], IIf([tblConvert].[Cost Type] Is Not Null,[tblConvert].[E1],IIf([tblConvert_1].[Pay Item] Is Not Null,[tblConvert_1].[E1],Null)) AS E1 FROM (YourTable LEFT JOIN tblConvert ON YourTable.[Cost Type] = tblConvert.[Cost Type]) LEFT JOIN tblConvert AS tblConvert_1 ON YourTable.[Pay Item] = tblConvert_1.[Pay Item]; -- Build a little, test a little. "Warren Phillips" wrote: That will never happen. The values in both fields and the values I am wanting to populate in the new E1 field will always be unique for any given record. There will be multiple records that have a Cost Type of L or a Cost Type of M, etc.....or.....a Pay Item of 955000 or a Pay Item of 955300......but each record will contain one or the other. In other words, there would never be a Cost Type of L AND a Pay Item of 955000 in the same record, etc..... Thank you Karl!!!! "KARL DEWEY" wrote: Which gets pripority? If both IIFs are satisfied then which do you put in E1? -- Build a little, test a little. "Warren Phillips" wrote: I am trying to accomplish the following in a Microsoft Access 2007 QUERY. I want to evaluate a field called "Cost Type" for the following values and then populate a new field (E1) with a value based on the Cost Type. For example If Cost Type = L, populate E1 with 1405 If Cost Type = M, populate E1 with 1410 If Cost Type = S, populate E1 with 1480 If Cost Type = G, populate E1 with 1495 Then.....I want to evaluate a different field (Pay Item) for the following conditions and place a static value in the E1 field...and..these values a If Pay Item = 955000, populate E1 with 1490 If Pay Item = 955300, populate E1 with 1491 If Pay Item = 955200, populate E1 with 1492 If Pay Item = 955100, populate E1 with 1493 Is there any way to combine these conditions (evalutating 2 fields) into 1 statement Thank you Warren Phillips |
#5
|
|||
|
|||
If Statements (Nested??)
You could use the formula below. This if the field are text. If they are
numbers you will need to remove the quotes. E1: =Iif([Cost Type] = "L","1405",Iif([Cost Type] = "M","1410",Iif([Cost Type] = "S","1480",Iif([Cost Type] = "G","1495",Iif([Pay Item] = "955000","1490",Iif([Pay Item] = "955300","1491",Iif([Pay Item] = "955200","1492",Iif([Pay Item] = "955100","1493","") -- Rick "Warren Phillips" wrote: I am trying to accomplish the following in a Microsoft Access 2007 QUERY. I want to evaluate a field called "Cost Type" for the following values and then populate a new field (E1) with a value based on the Cost Type. For example If Cost Type = L, populate E1 with 1405 If Cost Type = M, populate E1 with 1410 If Cost Type = S, populate E1 with 1480 If Cost Type = G, populate E1 with 1495 Then.....I want to evaluate a different field (Pay Item) for the following conditions and place a static value in the E1 field...and..these values a If Pay Item = 955000, populate E1 with 1490 If Pay Item = 955300, populate E1 with 1491 If Pay Item = 955200, populate E1 with 1492 If Pay Item = 955100, populate E1 with 1493 Is there any way to combine these conditions (evalutating 2 fields) into 1 statement Thank you Warren Phillips |
#6
|
|||
|
|||
If Statements (Nested??)
Rick
This could not have worked out any better. It worked out perfectly. Thanks to everyone for taking the time to help. "rbeach" wrote: You could use the formula below. This if the field are text. If they are numbers you will need to remove the quotes. E1: =Iif([Cost Type] = "L","1405",Iif([Cost Type] = "M","1410",Iif([Cost Type] = "S","1480",Iif([Cost Type] = "G","1495",Iif([Pay Item] = "955000","1490",Iif([Pay Item] = "955300","1491",Iif([Pay Item] = "955200","1492",Iif([Pay Item] = "955100","1493","") -- Rick "Warren Phillips" wrote: I am trying to accomplish the following in a Microsoft Access 2007 QUERY. I want to evaluate a field called "Cost Type" for the following values and then populate a new field (E1) with a value based on the Cost Type. For example If Cost Type = L, populate E1 with 1405 If Cost Type = M, populate E1 with 1410 If Cost Type = S, populate E1 with 1480 If Cost Type = G, populate E1 with 1495 Then.....I want to evaluate a different field (Pay Item) for the following conditions and place a static value in the E1 field...and..these values a If Pay Item = 955000, populate E1 with 1490 If Pay Item = 955300, populate E1 with 1491 If Pay Item = 955200, populate E1 with 1492 If Pay Item = 955100, populate E1 with 1493 Is there any way to combine these conditions (evalutating 2 fields) into 1 statement Thank you Warren Phillips |
#7
|
|||
|
|||
If Statements (Nested??)
I think it could have worked out a lot better with Karl and Marsh's
suggestion to use data stored in tables rather than hard coding values into an expression. What happens to this solution when Cost Types are changed? You shouldn't build expressions that might require maintenance in the future. -- Duane Hookom MS Access MVP "Warren Phillips" wrote in message ... Rick This could not have worked out any better. It worked out perfectly. Thanks to everyone for taking the time to help. "rbeach" wrote: You could use the formula below. This if the field are text. If they are numbers you will need to remove the quotes. E1: =Iif([Cost Type] = "L","1405",Iif([Cost Type] = "M","1410",Iif([Cost Type] = "S","1480",Iif([Cost Type] = "G","1495",Iif([Pay Item] = "955000","1490",Iif([Pay Item] = "955300","1491",Iif([Pay Item] = "955200","1492",Iif([Pay Item] = "955100","1493","") -- Rick "Warren Phillips" wrote: I am trying to accomplish the following in a Microsoft Access 2007 QUERY. I want to evaluate a field called "Cost Type" for the following values and then populate a new field (E1) with a value based on the Cost Type. For example If Cost Type = L, populate E1 with 1405 If Cost Type = M, populate E1 with 1410 If Cost Type = S, populate E1 with 1480 If Cost Type = G, populate E1 with 1495 Then.....I want to evaluate a different field (Pay Item) for the following conditions and place a static value in the E1 field...and..these values a If Pay Item = 955000, populate E1 with 1490 If Pay Item = 955300, populate E1 with 1491 If Pay Item = 955200, populate E1 with 1492 If Pay Item = 955100, populate E1 with 1493 Is there any way to combine these conditions (evalutating 2 fields) into 1 statement Thank you Warren Phillips |
#8
|
|||
|
|||
If Statements (Nested??)
Warren Phillips wrote:
I am trying to accomplish the following in a Microsoft Access 2007 QUERY. I want to evaluate a field called "Cost Type" for the following values and then populate a new field (E1) with a value based on the Cost Type. For example If Cost Type = L, populate E1 with 1405 If Cost Type = M, populate E1 with 1410 If Cost Type = S, populate E1 with 1480 If Cost Type = G, populate E1 with 1495 Then.....I want to evaluate a different field (Pay Item) for the following conditions and place a static value in the E1 field...and..these values a If Pay Item = 955000, populate E1 with 1490 If Pay Item = 955300, populate E1 with 1491 If Pay Item = 955200, populate E1 with 1492 If Pay Item = 955100, populate E1 with 1493 Is there any way to combine these conditions (evalutating 2 fields) into 1 statement You could use the Switch function, which is less confusing than a bunch of nested IIf functions: Switch([Cost Type] = L,1405, [Cost Type] = M,1410, ...) & Switch([Pay Item] = 955000,1490, ...) As E1 BUT, it is not a good idea to include data values such as L, M. etc. and 1405, 1410, etc. anywhere except in a table. It would be better if you had a table with an primary key field for the L, M, etc and another field for the 1405, 1410. etc. THen you can join this table to your data table in the query to translate the L to 1405 automatically, Similarly for the pay items, either in a third table or combined into the second table. -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|