A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

If Statements (Nested??)



 
 
Thread Tools Display Modes
  #1  
Old May 12th, 2010, 10:59 PM posted to microsoft.public.access.queries
Warren Phillips
external usenet poster
 
Posts: 15
Default 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  
Old May 12th, 2010, 11:25 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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  
Old May 12th, 2010, 11:36 PM posted to microsoft.public.access.queries
Warren Phillips
external usenet poster
 
Posts: 15
Default 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  
Old May 13th, 2010, 04:59 AM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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  
Old May 13th, 2010, 02:21 PM posted to microsoft.public.access.queries
rbeach
external usenet poster
 
Posts: 48
Default 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  
Old May 13th, 2010, 02:50 PM posted to microsoft.public.access.queries
Marshall Barton
external usenet poster
 
Posts: 5,361
Default 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]
  #7  
Old May 13th, 2010, 07:25 PM posted to microsoft.public.access.queries
Warren Phillips
external usenet poster
 
Posts: 15
Default 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

  #8  
Old May 14th, 2010, 04:09 AM posted to microsoft.public.access.queries
Duane Hookom[_4_]
external usenet poster
 
Posts: 316
Default 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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:14 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.