creating relationships

[TC]

Where I live it is suburb, state, postcode; for example:

Walkerville, SA, 5081

There are about 8,000 of these. The same suburb name can occur in different
states (with different postcodes). A single postcode can cover several
suburbs (in the same state). So there is certainly the potential to have
tblSuburb, tblState and tblPostcode, & relate those together.

However, I just have a single table with 3 fields & 8,000 records. This is
because I do not need to store any additional informatiion about suburbs,
states or postcodes - so there is no duplication.

If I *did* need to store exatra information about suburbs, or states, or
postcodes, I would go the 3-table way, to avoid any duplication. For
example, if I needed to store the date that each postcode was first used in
this country, I would have tblPostcode containing postcode (pk) &
date-first-used.

HTH,
TC

 

[McDonald]

I have seen many debates on the city state zipcode thing. I want to know
what is the best way of setting up tables for them. Should I put them all
in one or should I create one for states and one with cities and zip codes
and counties that have a relationship between the two.

 

[Tim Ferguson]

I want to know
what is the best way of setting up tables for them.

Sorry: there is no "best way", there is only the "best way in your
particular situation".

What are you going to be doing with the data -- will you be sorting or
choosing on them; what types of errors do your users make; how important is
it if someone lives in W Point and someone else in West Point or Westpoint
or W. Point or etc? How many of these areas do you have; how stable are
they; is there anybody who is prepared to sit down and type them all in,
without errors?

Despite all the laws you see laid down in NGs like this one, there is only
rarely one special right way to do anything, although there are often
several blatantly wrong ones!

Best wishes


Tim F

 

[McDonald]

ok, I have decided to go the three table way, just incase i need to add more
information, like counties, boros or twp. What I need to know now is how to
link them in a relationship. What type of relationship should I use and
should I enforce the referential integrity?

 

[Tim Ferguson]

ok, I have decided to go the three table way, just incase i need to
add more information, like counties, boros or twp. What I need to
know now is how to link them in a relationship. What type of
relationship should I use and

You haven't answered any of the questions (and there is no reason why you
should have to, unless you want to) so it is not possible to suggest much.
What three tables are you going to create? The correct relationships depend
totally on what entities you have identified and how they relate to each
other.

should I enforce the referential
integrity?

I do not see any point in using Access if you are not going to use the
engine to look after your data. A relationship only exists if you select
Ref Integrity; otherwise it is just a hint to the query grid and not worth
wearing your mouse out for.

Best wishes


Tim F

 

[McDonald]

I will be choosing on them, for instance I have a client table with a city
state and zip field I also will be choosing on them for a suplier table an
informant table and so on. This information will be used to sort also. For
example 3 suppliers are located in West Point. Also how many clients live in
PA or NY etc. I wasn't planning on typing them in all at once, Just build
on them as time goes on. I want the data to appear as the U.S. Postal
Service reccomends Such as West Point. Because I will also be creating
mailers from these addresses. Thank you