Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculated Field and Calculated Item in Pivot Table | Excel Discussion (Misc queries) | |||
PivotTable:Using a calculated field result in another calculated f | Excel Worksheet Functions | |||
pivot table formulas for calculated field or calculated item | Excel Discussion (Misc queries) | |||
Excel2000: Calculated field name in ODBC Query | Excel Discussion (Misc queries) |