put data in table field from another table.

[Guest]

I have a table with student data. One of the fields is county. I also have a
table with cities and their corresponding county. How can I populate the
correct county in the student table based on the city they indicated?

 

[Rick B]

You wouldn't.

Using a relational database, you only store that in the CITIES table. You
can DISPLAY the country in your forms, reports, and queries by including
both tables in your source and creating a relationship between the two
tables based on the CITY.

This is the whole point of a relational database. Only store data once, but
be able to display it or use it with many records.

 

[Guest]

Rick B is right. This might be a better job for a relational database.
However, if you are talking less than 500,000 records or less you could
choose to use a flat database. I would not use city as my county indicator. I
would choose the zip code. The zip to County would be more accurate
http://zip4.usps.com/zip4/welcome.jsp can help with zip to county lookups.
Keeping all of the contact information a flat file is also cinsistant with a
"Master Data" for customer/student contact.

Using the city can be a bad choice, for example, the city of Washington, San
Juan, etc. Sometimes there is ambiguity in the City naming and in various
States, foreighn countries. Review the Outlook Contact database.
State/Province, Postal Code, etc.. Do you want Mexican States Mexicalli and
"Baja" for example?

A flat file can be beneficial when you can't maintian the relational
database. Referential integrity may require an entry to County. You may not
know it or it may be blank. For example, you may have a runaway, Foster Child
or a Child of an Undocumented without a valid address: Living in a Car,
camping at the river, or living under a bridte. You can always have an
Unknown.