Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Sep 2006
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    repeating data (access 2003)

    have table with cities and counties. some cities have two counties. can we have two cities with different county id's in same table? thanks, chancer

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: repeating data (access 2003)

    What exactly does that mean? Is it one city that is partly in one county and partly in another one, or are they two different cities in different counties, but with the same name?

    PS was the reply to <post:=605,108>post 605,108</post:> helpful?

  3. #3
    New Lounger
    Join Date
    Sep 2006
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: repeating data (access 2003)

    Sorry for not being clearer....

    Current db is to be set up with the following tables: tblCounties, tblCities (with key from counties), tblDoctors (with county, hospital, city keys), tblHospitals (with city, county keys), tblPracticeAdmins (with county, hospital, city keys).

    My co-worker recently took an Access class and the intructor was adamant about no repeating data. But several cities are located in 2 or even 3 counties, and we need to know if the following field enteries would work:

    tblCities
    Primary Key CityName CountyID
    1 Mocksville 10 (Burke Co.)
    1 Mocksville 11 (Caldwell Co.)

    Would this repeating city crash the db or cause major problems long term? My co-workers says to list a city with only the one county ID number. The db is currently used to print out reports and a quarterly mailing list. The boss in charge wants mailings to go by county say all the doctors and practice administrators in Caldwell county but not Burke. If we list Mocksville as Burke only then we would be missings some folks on the mailing list.

    Or have we confused ourselves?

    Any help is appreciated.

    Thanks, Chancer

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: repeating data (access 2003)

    Sorry, I have to repeat my question, but I'll use your example.

    Is Mocksville in Burke County the same city as Mocksville in Caldwell County (i.e. part of the city is in Burke Co., and another part is in Caldwell Co.), or are they different cities that happen to have the same name?

    If the former, you actually have a many-to-many relationship between counties and cities: one county contains multiple cities, and one city can belong to multiple counties. A many-to-many relationship is implemented by creating an intermediary table tblCountiesCities, containing only two fields: CountyID from tblCounties and CityID from tblCities. The combination of these two fields is the primary key in tblCountiesCities. Each record in this table represents a unique county/city combination.
    Most cities belong to only one county, so they have only one record in tblCountiesCities, but if a city belongs to two or three counties, it'll have two or three records.

  5. #5
    New Lounger
    Join Date
    Sep 2006
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: repeating data (access 2003)

    Thanks the help. We will try it out.

    Chancer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •