Calculated field using Query Grid and IF statement

[Peter]

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?

 

[Baz]

1. You haven't got a file with two fields, you've got a table with two
fields. You can (and usually will) create many tables in an Access file: it
is not an Excel spreadsheet where you are restricted to just one
2-dimensional representation of your data, it's much more powerful than
that.

2. Field_2 in your table is redundant. You will generate Field_2 as part of
your query output, you don't need to store it in the table.

3. Your Excel formula would work in Access almost as it is (although it is
not necessarily the best way of achieving what you want). In the top row of
the query design grid (the row labelled "Field") type the following into a
new column:

Iif (Field_1=1, "marigold", Iif (Field_1=2, "sunflower", Iif (Field_1=3,
"rose","")))

Note that (i) the double "I" is deliberate, this is the Access "Immediate
If" function and (ii) Access automatically gives the name "Expr1" to the
calculated field (you can change this if you like, but you can't change it
to Field_2 unless you first delete the field of the same name from your
table)

4. Instead of using the Iif function, you can achieve the same thing using
the Switch function, which has a simpler structure:

Switch (Field_1=1, "marigold", Field_1=2, "sunflower", Field_1=3, "rose")

5. The best way to do this, though, would be to create a lookup table for
your flower names, but I think you will need to learn a bit more about
relational database design before tackling that.

 

[Douglas J. Steele]

4a. Instead of using the Switch function, for this particular example you
can achieve the same thing using the Choose function, which has an even
simpler structure:

Choose([Field_1], "marigold", "sunflower", "rose")

I definitely agree, though, that option 5 is the best.

 

[Peter]

Thankyou Baz. I will give it a go. Youve given me a good couple of leads
there. taa again.

 

[Baz]

Thx Doug, I have a blind spot about the Choose function, I always forget
it's there until someone mentions it in a forum!

4a. Instead of using the Switch function, for this particular example you
can achieve the same thing using the Choose function, which has an even
simpler structure:

Choose([Field_1], "marigold", "sunflower", "rose")

I definitely agree, though, that option 5 is the best.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

1. You haven't got a file with two fields, you've got a table with two
fields. You can (and usually will) create many tables in an Access file:
it is not an Excel spreadsheet where you are restricted to just one
2-dimensional representation of your data, it's much more powerful than
that.

2. Field_2 in your table is redundant. You will generate Field_2 as part
of your query output, you don't need to store it in the table.

3. Your Excel formula would work in Access almost as it is (although it
is not necessarily the best way of achieving what you want). In the top
row of the query design grid (the row labelled "Field") type the
following into a new column:

Iif (Field_1=1, "marigold", Iif (Field_1=2, "sunflower", Iif (Field_1=3,
"rose","")))

Note that (i) the double "I" is deliberate, this is the Access "Immediate
If" function and (ii) Access automatically gives the name "Expr1" to the
calculated field (you can change this if you like, but you can't change
it to Field_2 unless you first delete the field of the same name from
your table)

4. Instead of using the Iif function, you can achieve the same thing
using the Switch function, which has a simpler structure:

Switch (Field_1=1, "marigold", Field_1=2, "sunflower", Field_1=3, "rose")

5. The best way to do this, though, would be to create a lookup table for
your flower names, but I think you will need to learn a bit more about
relational database design before tackling that.

 

[Baz]

See Doug's post, he has reminded us that there is an even simpler function.

 

[Peter]

Hello Douglas,
Thanks for the suggestion I will try it.

On another matter, I noticed that your name has appeared in among other
places, questions on playing sound files using MS Access interacting with the
MCI (media control interface.)

Well...I am interested in sometime down the track in using your suggestions
in your paper entitled "Sounds Good to Me....". I have been given a simple
Access database which relates my CD collection details (from artist to cds to
tracks). This allows me to run queries to throw up playlists according to
criteria like artist year and genre.

My next step is to use something like what you have described in your paper
i got from your website. However, I need to take some baby steps on this one
first as your paper is too advanced for me at the moment. And so i need, I
think, something that will help me learn more generally about MCI before I
embark on this project. Is MCI described in a text book somewhere? My goal
is to use MS Access (once I know more about it) to control wav. file playing.
A subset of the wav files would be thrown up by a query (= a playlist) and
then passed to a media player such as WMP or Itunes. Its not the querying I
am going to have trouble with.. its the passing of the results to a sound
player that is daunting me. I am familiar with VBA (Excel) and I am
envisioning that this will be handy to create events to trigger the query and
then to trigger the passing of the resulting wav files to the player.

Are there any suggestions for pointing me in the right direction to learn in
a methodical and self paced way about MCI and its relationship to my problem?
(That is of course assuming that I am on the right track in investigating MCI
as a necessary part of this jigsaw.

Your thoughts would be very much appreciated. No hurry whatsoever. Thanks
in advance.
--
Peter

4a. Instead of using the Switch function, for this particular example you
can achieve the same thing using the Choose function, which has an even
simpler structure:

Choose([Field_1], "marigold", "sunflower", "rose")

I definitely agree, though, that option 5 is the best.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

1. You haven't got a file with two fields, you've got a table with two
fields. You can (and usually will) create many tables in an Access file:
it is not an Excel spreadsheet where you are restricted to just one
2-dimensional representation of your data, it's much more powerful than
that.

2. Field_2 in your table is redundant. You will generate Field_2 as part
of your query output, you don't need to store it in the table.

3. Your Excel formula would work in Access almost as it is (although it is
not necessarily the best way of achieving what you want). In the top row
of the query design grid (the row labelled "Field") type the following
into a new column:

Iif (Field_1=1, "marigold", Iif (Field_1=2, "sunflower", Iif (Field_1=3,
"rose","")))

Note that (i) the double "I" is deliberate, this is the Access "Immediate
If" function and (ii) Access automatically gives the name "Expr1" to the
calculated field (you can change this if you like, but you can't change it
to Field_2 unless you first delete the field of the same name from your
table)

4. Instead of using the Iif function, you can achieve the same thing using
the Switch function, which has a simpler structure:

Switch (Field_1=1, "marigold", Field_1=2, "sunflower", Field_1=3, "rose")

5. The best way to do this, though, would be to create a lookup table for
your flower names, but I think you will need to learn a bit more about
relational database design before tackling that.

 

[Peter]

Thankyou both.
--
Peter

Thx Doug, I have a blind spot about the Choose function, I always forget
it's there until someone mentions it in a forum!

4a. Instead of using the Switch function, for this particular example you
can achieve the same thing using the Choose function, which has an even
simpler structure:

Choose([Field_1], "marigold", "sunflower", "rose")

I definitely agree, though, that option 5 is the best.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

1. You haven't got a file with two fields, you've got a table with two
fields. You can (and usually will) create many tables in an Access file:
it is not an Excel spreadsheet where you are restricted to just one
2-dimensional representation of your data, it's much more powerful than
that.

2. Field_2 in your table is redundant. You will generate Field_2 as part
of your query output, you don't need to store it in the table.

3. Your Excel formula would work in Access almost as it is (although it
is not necessarily the best way of achieving what you want). In the top
row of the query design grid (the row labelled "Field") type the
following into a new column:

Iif (Field_1=1, "marigold", Iif (Field_1=2, "sunflower", Iif (Field_1=3,
"rose","")))

Note that (i) the double "I" is deliberate, this is the Access "Immediate
If" function and (ii) Access automatically gives the name "Expr1" to the
calculated field (you can change this if you like, but you can't change
it to Field_2 unless you first delete the field of the same name from
your table)

4. Instead of using the Iif function, you can achieve the same thing
using the Switch function, which has a simpler structure:

Switch (Field_1=1, "marigold", Field_1=2, "sunflower", Field_1=3, "rose")

5. The best way to do this, though, would be to create a lookup table for
your flower names, but I think you will need to learn a bit more about
relational database design before tackling that.

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?

 

[Douglas J. Steele]

While there are a couple of sites out there that have more information about
MCI, I'm afraid I don't have the references. I think they talk about MCI in
the Access Developer Handbook (see http://www.developershandbook.com/ for
details), but I'm not positive (and my copy of the book isn't handy at the
moment.)

The article you mention, though, includes all the code you need to play an
MP3.

 

[Peter]

Thanks Douglas. I will keep your suggestion in mind as I go into unchartered
waters.