automatically put data in a field based upon criteria

M

Mack

I have a table that I've added an empty column to, and I'd like Access to
automatically place values in each field in the column based on criteia of
another field in each record.

For example, the empty column is called building, I have 10 building names
and need the correct building name to be filled into the field based on a
number is in another field in the same record.

Seems like this should be easy, but I can't seem to figure it out. I tried
changing the data type of the building field to a lookup, but got lost trying
to get the correct information in there to do what I need it to do.
 
R

rowiga

Entering data directly into the table isn't recommended. You should be using
a form.

If you can already lookup the data from another table based on information
in the current record it isn't necessary or desireable to store it again. If
I misunderstood you then you could use Select Case to do what you want. Use
it in the AfterUpdate event of the related field on the form.

Private Sub YourControlName_AfterUpdate()
Select Case Me.YourControlNAme
Case "Terrier"
Me.[Building] = "KennelA"
Case "Shitzu"
Me.[Building] = "KennelB"
Case "Great Dane"
Me.[Building] = "KennelC"
Case "Boxer"
Me.[Building] = "KennelD"
End Select
End Sub
 
R

Rick B

If you already have a field that identifies the building (building number)
then placing the building name in the same table in a different field would
be redundant. You are missing the point of a RELATIONAL database. You
should build a new table that has two fields. BuildingNumber and
BuildingName. Then, create a relationship to link your existing table's
building number field to the new table.

When you build a query, report, or form, simply include both tables. You
can then print or display the building name or number without having to
store the data in the original table twice. Think about it. If you have
1,000 records and each one contains the one-digit building number, you don't
want to add another field with the spelled-out name for the building and
increase the size and overhead of your database. Even worse, what if a
building changes names? (Trump is selling off real estate all the time :)
Do you want to go change the name one time in a maintenance table, or do you
wnat to have to update all the detail records.

This is the whole point of relational datbases. You get this down and you
can do other cool stuff with the data. for example, when you add a new
record, you can have a drop down that lists all the available building
numbers and names. You can pick one from the list, or simply enter the
number and the form will know which one you want and you can display the
actual name for the user.

Hope that helps,

Rick B
 
M

Mack

Thank you - this helps.

I did actually create a buildings table. I do have another little problem,
and that is that I'm using a portion of an IP address to identify which
building a record belongs to. The problem is that some buildings have 2
possible numbers in the beginning portion of the IP address that tell me what
building the record belongs to. And in one case, one of the beginning IP
numbers is at a possible 3 buildngs, in which case I have to figure out what
building the record belongs to based on another field in the record.

Then on top of all that, I am trying to get all of the correct building
records to each of 4 techs in a report (each tech is responsible for a few
buildings). Its confusing to me; I know there's probably about a hundred
different ways to tackle this. I do appreciate your reply it has helped to
get me back on track :)
 
Top