Need help to build an IF / Then expression in access form

[Sandy Crowley]

I've been reading other questions and answers but I'm not fluent enough to
know how to translate them to what I need.

I have a form with the following fields:
Owner 1 (pick list to choose one name of 27),
Owner 2 (pick list to choose one name of 27),
Owner 3 (pick list to choose one name of 27);
Studio 1 (pick list to choose one name of 17),
Studio 2 (pick list to choose one name of 17),
Studio 3 (pick list to choose one name of 17).

I would like the following result:
If "Bob" or "Todd" or "Lisa" are selected in the [Owner 1] field Then
[Studio 1] = "NC", If "Jim" or "Doug" or "Brian" are selected in the [Owner
1] field Then Studio 1 = CA, etc.

I hope this makes sense.

I've been poking around the expression builder and I can figure some of it
out with my knowledge of writing formulas in Excel but, I don't have a lot of
learning curve time.

Any help is greatly appreciated.

 

[Jeff Boyce]

Sandy

If all of your If x then y are pre-ordained, then why bother
showing/deteriming the "y" value (if you know X, you know Y)?

In a well-normalized relational database, the table structure would allow
this information, and you'd use a query to associate the Owner with his/her
"studio".

"How" depends on "what" -- you'll need to provide a bit more description of
the data structure you're using.


Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[KARL DEWEY]

You can build a translation table like this --
[Owner 1] [Studio 1]
Bob NC
Todd NC
Lisa NC
Jim CA
Doug CA
Brian CA
Left join your other table to the translation table on [Owner 1].

But when I see fields named [Owner 1] and [Studio 1] it leads me to believe
you table structure is wrong in that you have a spreadsheet instead of
relational tables.

 

[Sandy Crowley]

You both are right. I probably don't have my relational tables structured
properly. I have a table for the main body of data and separate tables for
Studio, Owner, and Office Location. Should I run the analyzer to get my db in
order?
--
Thank you,

scrowley(AT)littleonline.com

You can build a translation table like this --
[Owner 1] [Studio 1]
Bob NC
Todd NC
Lisa NC
Jim CA
Doug CA
Brian CA
Left join your other table to the translation table on [Owner 1].

But when I see fields named [Owner 1] and [Studio 1] it leads me to believe
you table structure is wrong in that you have a spreadsheet instead of
relational tables.

I've been reading other questions and answers but I'm not fluent enough to
know how to translate them to what I need.

I have a form with the following fields:
Owner 1 (pick list to choose one name of 27),
Owner 2 (pick list to choose one name of 27),
Owner 3 (pick list to choose one name of 27);
Studio 1 (pick list to choose one name of 17),
Studio 2 (pick list to choose one name of 17),
Studio 3 (pick list to choose one name of 17).

I would like the following result:
If "Bob" or "Todd" or "Lisa" are selected in the [Owner 1] field Then
[Studio 1] = "NC", If "Jim" or "Doug" or "Brian" are selected in the [Owner
1] field Then Studio 1 = CA, etc.

I hope this makes sense.

I've been poking around the expression builder and I can figure some of it
out with my knowledge of writing formulas in Excel but, I don't have a lot of
learning curve time.

Any help is greatly appreciated.

 

[John W. Vinson]

You both are right. I probably don't have my relational tables structured
properly. I have a table for the main body of data and separate tables for
Studio, Owner, and Office Location. Should I run the analyzer to get my db in
order?

That might be helpful, but the analyzer is pretty limited. This kind of
problem really requires a USB interface - Using Someone's Brain, in particular
yours!

There are some good resources for learning how to set up a properly normalized
set of tables at:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials