Calculated field using IF statements in the Query Grid
 

8:23 PM 29/12/2007

I am only a new MS Access user and so I would like some help, please, in
solving this problem using the Access Query grid.

I have an Access 2003 file with two fields as follows:

Field_1 Field_2
3 ..
1 ..
2 ..
1 ..
3 ..
2 ..
2 ..
3 ..
2 ..

My objective is to make Field_2 a calculated field by running a query that
recognises the value of Field_1 and allocates a text string according to a
simple rule. The rule is as follows:
A value of 1 in Field_1 stands for "marigold";
A value of 2 in Field_1 stands for "sunflower"; and
A value of 3 in Field_1 stands for "rose".

So, using my yet-to-be-formulated query, Field_2 would look like this:

Field_1 Field_2
3 rose
1 marigold
2 sunflower
1 marigold
3 rose
2 sunflower
2 sunflower
3 rose
2 sunflower

If I was using Excel, I would solve the problem using the IF function. For
example (assuming Field_1 in Access was Col A in Excel and Field_2 was Col B
in Excel),
I would put the following formula in Col B in the first row and copy it
down the colum:
=IF (A1=1, "marigold", IF (A1=2, "sunflower", IF (A1=3, "rose",)))

Knowing Microsoft, the Excel formula above should translate fairly closely
into MS Access but I do not know how to actually phrase it in the Query grid.
The query grid thingy is a little unweildy for me at my current stage of
learning about Access.

Could someone please help me?

--
Peter

Calculated field using IF statements in the Query Grid
 

You may get a quicker reply if you post this to an Access users group.
--
HTH,
Barb Reinhardt



"Peter" wrote:

8:23 PM 29/12/2007

I am only a new MS Access user and so I would like some help, please, in
solving this problem using the Access Query grid.

I have an Access 2003 file with two fields as follows:

Field_1 Field_2
3 ..
1 ..
2 ..
1 ..
3 ..
2 ..
2 ..
3 ..
2 ..

My objective is to make Field_2 a calculated field by running a query that
recognises the value of Field_1 and allocates a text string according to a
simple rule. The rule is as follows:
A value of 1 in Field_1 stands for "marigold";
A value of 2 in Field_1 stands for "sunflower"; and
A value of 3 in Field_1 stands for "rose".

So, using my yet-to-be-formulated query, Field_2 would look like this:

Field_1 Field_2
3 rose
1 marigold
2 sunflower
1 marigold
3 rose
2 sunflower
2 sunflower
3 rose
2 sunflower

If I was using Excel, I would solve the problem using the IF function. For
example (assuming Field_1 in Access was Col A in Excel and Field_2 was Col B
in Excel),
I would put the following formula in Col B in the first row and copy it
down the colum:
=IF (A1=1, "marigold", IF (A1=2, "sunflower", IF (A1=3, "rose",)))

Knowing Microsoft, the Excel formula above should translate fairly closely
into MS Access but I do not know how to actually phrase it in the Query grid.
The query grid thingy is a little unweildy for me at my current stage of
learning about Access.

Could someone please help me?

--
Peter

Calculated field using IF statements in the Query Grid
 

oops i am so sorry. i thought i did. pls accept my apology.
--
Peter


"Barb Reinhardt" wrote:

You may get a quicker reply if you post this to an Access users group.
--
HTH,
Barb Reinhardt



"Peter" wrote:

8:23 PM 29/12/2007

I am only a new MS Access user and so I would like some help, please, in
solving this problem using the Access Query grid.

I have an Access 2003 file with two fields as follows:

Field_1 Field_2
3 ..
1 ..
2 ..
1 ..
3 ..
2 ..
2 ..
3 ..
2 ..

My objective is to make Field_2 a calculated field by running a query that
recognises the value of Field_1 and allocates a text string according to a
simple rule. The rule is as follows:
A value of 1 in Field_1 stands for "marigold";
A value of 2 in Field_1 stands for "sunflower"; and
A value of 3 in Field_1 stands for "rose".

So, using my yet-to-be-formulated query, Field_2 would look like this:

Field_1 Field_2
3 rose
1 marigold
2 sunflower
1 marigold
3 rose
2 sunflower
2 sunflower
3 rose
2 sunflower

If I was using Excel, I would solve the problem using the IF function. For
example (assuming Field_1 in Access was Col A in Excel and Field_2 was Col B
in Excel),
I would put the following formula in Col B in the first row and copy it
down the colum:
=IF (A1=1, "marigold", IF (A1=2, "sunflower", IF (A1=3, "rose",)))

Knowing Microsoft, the Excel formula above should translate fairly closely
into MS Access but I do not know how to actually phrase it in the Query grid.
The query grid thingy is a little unweildy for me at my current stage of
learning about Access.

Could someone please help me?

--
Peter