Calculated field using Query Grid and IF statement

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Peter - 29 Dec 2007 10:38 GMT

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?

Signature

Peter
Was this post helpful to you?

--
Peter

Reply to this Message

Baz - 29 Dec 2007 12:01 GMT

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
>
[quoted text clipped - 50 lines]
>
> Could someone please help me?

Reply to this Message

Douglas J. Steele - 29 Dec 2007 12:22 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(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:
[quoted text clipped - 85 lines]
>>
>> Could someone please help me?

Reply to this Message

Baz - 29 Dec 2007 12:29 GMT

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
[quoted text clipped - 93 lines]
>>>
>>> Could someone please help me?

Reply to this Message

Peter - 29 Dec 2007 12:58 GMT

Thankyou both.

Signature

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!
[quoted text clipped - 96 lines]
> >>>
> >>> Could someone please help me?

Reply to this Message

Peter - 29 Dec 2007 12:56 GMT

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.

Signature

Peter

Reply to this Message

Douglas J. Steele - 29 Dec 2007 15:46 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Hello Douglas,
> Thanks for the suggestion I will try it.
[quoted text clipped - 42 lines]
> Your thoughts would be very much appreciated. No hurry whatsoever. Thanks
> in advance.

Reply to this Message

Peter - 29 Dec 2007 20:53 GMT

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

Signature

Peter

> 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
[quoted text clipped - 51 lines]
> > Your thoughts would be very much appreciated. No hurry whatsoever. Thanks
> > in advance.

Reply to this Message

Peter - 29 Dec 2007 12:27 GMT

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

Signature

Peter

> 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
[quoted text clipped - 82 lines]
> >
> > Could someone please help me?

Reply to this Message

Baz - 29 Dec 2007 12:32 GMT

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

> Thankyou Baz. I will give it a go. Youve given me a good couple of leads
> there. taa again.
[quoted text clipped - 98 lines]
>> >
>> > Could someone please help me?

Reply to this Message