relation design

[Aaron]

I have a situation I am not sure how to handle. I need to log where clients
live (either in a city a boro or a township) and then specify the name.
Should I create a separate table for each city boro and twp. Then a name
table that I can link to one of the three. I also have a table set up with
a city state zip and county field. Maybe I can include it in this table some
way. I am not sure what to do and any help, advice I could get would be a
great help. Thank you

 

[TC]

See if this helps:

http://support.microsoft.com/support/kb/articles/Q100139.ASP

HTH,
TC

 

[Aaron]

TC,
Thanks for recommending the article, it always helps to see the best
normalizations. In my case, I believe I have everything normalized to the
best practicality, however, this is the only situation where I am having
trouble. My database is mainly intended to use reports and collect
statistics for the cities, boroughs or townships. These reports are vital
to expediting business. If I explain what I need to do that might help in
completing this project. I need to know where the customers live in a
specific area, either a city boro or twp. and then need to designate the
actual name of it. So in the end I need to know how many people live in
Bellvue boro or how many people live in Ohio Twp. Then on my reports, If
they live in a city or boro it prints out on one line, or if they live in a
township it prints out on another. I any case, I cannot figure out the best
way to set these up table wise. Should I create a separate table for each
or just one location table that on a form I can select from three in an
option group and have a text box to type in the actual name, be it a city
boro or township. As always, any bit of advice helps and thank you for
your input, any further, in detail response would be greatly appreciated.
Again thank you.

 

[John Vinson]

Should I create a separate table for each
or just one location table that on a form I can select from three in an
option group and have a text box to type in the actual name, be it a city
boro or township.

I'd certainly do the latter. Your Locations table could have a field
for LocationName ("Brattlesboro") and for LocationType ("city", "twp"
and so on). After all, a township could incorporate itself as a boro
(if your locale names are like the "township", "village", "town" and
"city" designations with which I am familiar).

 

[McDonald]

That makes sense, under location type what settings would I need to have
under table. Also, how would I connect that to an option group under the
form. Thank You

 

[John Vinson]

That makes sense, under location type what settings would I need to have
under table. Also, how would I connect that to an option group under the
form. Thank You

I'd suggest using a Listbox rather than an Option Group - an option
group must be bound to a numeric field and you don't really need the
added complexity. Just have a text field in your table LocationType,
and a tiny (three row, one field) LocationTypes table; put a Listbox
control on the form bound to the LoctionType field, using the
LocationTypes table as its rowsource. The user can simply click on the
desired row. And if your jurisdiction ever changes to add "suburb" or
"village" or "municipality", you can simply edit the table to include
them.

 

[McDonald]

Thanks John I appreciate it. I have another post you could maybe help me
with. It is under McDonald.

I want to be able to have cities states zipcodes and counties all fill in
automatically. Should I create:

A table for City
One for State (With full names and abbreviations)
one for counties
one for zipcodes
and create a relationship between them.
Or should I create them all into one table (CSaZC).
Cant really decide what I want to do.