Relational data, please help

[Aaron]

I am creating tables (about 20) in which all of them have fields for city
state zip and county. Is there a way that I can have a separate table for
city state zip and county and have that information selectable when I go to
enter data on the tables rather than type it in on each table. Please be
explicit because I am somewhat unfamiliar with relationships. Thank You,
Aaron

 

[Ken Snell]

Yes. Set up a table (tblAddresses) with the following fields:
AddressPK (primary key - likely an autonumber)
CityName
StateName
ZipName
CountyName

Then you can use combo box in a form to select from the above table, using
an SQL statement similar to this in the combo box's RowSource:
SELECT * FROM tblAddresses

Set the combo box's BoundColumn to 1. Set the ColumnCount to 5. Set the
ColumnWidths to 0";1";1";.5";1".

Then you can bind the combo box to a field in another table (the form's
RecordSource). You also can have the state, zip, and county be displayed on
the form in separate textboxes if you adapt the code given here:
http://www.mvps.org/access/forms/frm0058.htm

 

[Ken Snell]

In addition to what I posted in the earlier message, your other tables would
have just one field in them for the "address", not four fields. This one
field will hold the value of the AddressPK field from tblAddresses, thereby
allowing a link between the table and tblAddresses.

You then would set up a relationship between that one field and the
AddressPK field in the tblAddresses table.

 

[TC]

I am creating tables (about 20) in which all of them have fields for city

state zip and county.
(snip)

This sounds a little suspicious! Have you read:
http://support.microsoft.com/support/kb/articles/Q100139.ASP ?

TC

 

[Aaron]

Thank you for you help, this should get me on my way. If I run into trouble
I will post again. Thanks

 

[Aaron]

What about a gender listing, i.e. male female, should I set up a table with
male and female and use those values or is there another way of doing this.
I would also like to do this for race. i.e. white, black, hispanic, etc.

 

[TC]

With genders, people would normally store the relevant code (M, F,
etc.) in each record directly. This is probably because there is a
very small number of genders; all of them are known "up front"; and
there is common agreement on how to encode them (M, F, etc.)

However, if there was certain data that was associated with each
gender specifically, that extra data could be stored in a seperate
table, with one record for each gender; for example:

tblNormalRanges
Gender text(1) (PK)
NormalLow number
NormalHigh number

As for race, that's a slightly different issue. There are many more
races than genders, & you might not know them all "up front". (That
is, you are far more likely to discover an extra race that you need to
record, compared to discovering an extra gender that you need to
record!) I feel that most developers would validate the race against a
table of valid races. But then, some developers would store the race
directly into the data record, and others would only store a unique
code which stood for that race.

That is, some developers would have:

tblValidRaces
white
black
hispanic

and would store the actual race ("white", "black" or whatever) in the
other data records,

but other developers would have:

tblValidRaces
WH white
BL black
HS hispanic

and would store WH, BL or whatever in the other data records.

Personally, I incline to the latter.

HTH,
TC

 

[Adrian Jansen]

Thanks John and Tim.

Having got all the entries that I need to filter on as numerics, I think I
will leave it that way, and just stick with the 2 entry table. Yes, there
are cases where we might want to display "Male" and "Female" instead of just
M and F, so this is better than hard coding funny IIf statements.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

Any easier way ?

=IIf([Gender]=1,"F","M")

=Mid$("FM",[Gender])

=Chr$(Asc("F") + ([Gender]-1) * (Asc("M")-Asc("F")))

Can you do this with a format ?

Almost certainly, using the extra semi-colon syntax, but I can't remember
the correct order off-hand.


By the way, I have just thought of one advantage of using a separate table,
which is that it makes it very easy to translate the database for
paediatrics, using "B" and "G" for the text values...


All the best


Tim F