Creating Expressions in access

[Jcraig713]

I am new to access and have only written a few simple expressions (name
trimming etc.) in Access. The task I am trying to accomplish now is beyond
me and I hope someone can help!

I have created a DB where an end user enters in data based on building
number (19 total buildings). I would like 2 other fields (phone number and
building name) to be entered automatically based on what building numberis
entered:

Example: if building number - 01 then name = xxx and phone number = xxx if
no, check building number 02, if yes, building name = xxx and phone = xxx if
no, then check if value = 03 and so forth through building 19. If building
number = none of the preset values, name and number fields = blank.

I hope someone can help and if so, I thank you in advance for your time and
assistance.

 

[schasteen]

I don't know enough about you table designs, but instead of writing an
expression, you need to a table with the building details and link it to your
other tables. Or, if just add the fields to your existing table and enter
the data. For only 19 records, that will be quicker than writing any
expression.

 

[Jcraig713]

Thanks for your time... it is appreciated. As for background on my table
design, remember I am a novice but I have over 500 records in my db rigth now
so saving keystrokes by this auto-fillin feature was my objective. Every
student is a record and I keep info on entity number, entity name and entity
phone number along with student name address , phone, gender, grade etc.
This data is housed in 1 main table with column headings along the top.

I have never tied in another table (entity details) to my main table nor
have I thought along those lines. Thanks for the tip though. I will try and
figure that one out!

 

[schasteen]

Your best bet is to create a table with building information. Create a
queary with a relationship on the building number. On your form you can the
create a comb box control to select the building name and it will then show
the correct name and number. Learning this will greatly help you take
advantage on what a relational database can do. You may want to search on
normalizing data.

 

[John Vinson]

Thanks for your time... it is appreciated. As for background on my table
design, remember I am a novice but I have over 500 records in my db rigth now
so saving keystrokes by this auto-fillin feature was my objective. Every
student is a record and I keep info on entity number, entity name and entity
phone number along with student name address , phone, gender, grade etc.
This data is housed in 1 main table with column headings along the top.

I have never tied in another table (entity details) to my main table nor
have I thought along those lines. Thanks for the tip though. I will try and
figure that one out!

It's good advice. You're using a relational database, not a
spreadsheet! The whole POINT of the relational model is that you store
information *once*, and then use Queries to look it up as needed.
Storing the building information repeatedly and redundantly is simply
bad design and is not taking advantage of the power (and the very
purpose!) of Access.

John W. Vinson[MVP]