Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Several relationships from a single table

Discussion in 'Information Technology' started by Brian, Nov 1, 2005.

  1. Brian

    Brian
    Expand Collapse
    Guest

    I use a City-State reference table in my genealogy database for three fields
    in another table - the birth, death, and primary residence locations for each
    person.

    The way I originally defined the relationships in the Relationships Window
    was by using the Show Table option and inserting the table three times.
    Access shows the tables as City-State_1, City-State_2, City-State_3. Is there
    a better way to establish the relationships?

    I miss being able to expand the records in my original table with the "+"
    button. Evidently setting up the relationships as I did does not allow to
    view related records anymore without a query being performed.
     
  2. Loading...

    Similar Threads Forum Date
    India Hindu fanatics behind several sensational cases: Govt Breaking News Jan 11, 2013
    India Raid finds several schools locked Breaking News Sep 23, 2012
    Sikh News Several trains cancelled due to protest by Sikh bodies Breaking News Nov 4, 2009
    Sikh News Flood-like situation in several Punjab districts (Calcutta News) Breaking News Aug 14, 2008
    Sikh News Flood-like situation in several Punjab districts (IANS via Yahoo! India News) Breaking News Aug 14, 2008

  3. tina

    tina
    Expand Collapse
    Guest

    hmm, sounds as though your "persons" table could be normalized a bit in this
    area. if you have fields named "birth location", "death location", and
    "primary residence location", then you're storing data (birth, death,
    primary residence) in fieldnames . that's usually an indication that it is
    appropriate to put that data into a subordinate table. suggest you move
    "locations" into a child table of your people table, as

    tblPeople
    PersonID (primary key)
    FirstName
    LastName
    (other fields that describe a person)

    tblLocationTypes
    LocTypeID (primary key)
    LocTypeName
    (records in this table would be "birth", "death", "primary residence", and
    whatever other location types are appropriate.)

    tblPeopleLocations
    PLocID (primary key)
    PersonID (foreign key from tblPeople)
    LocTypeID (foreign key from tblLocationTypes)
    CityStateID (foreign key from tblCityState)

    relationships would be:
    tblPeople 1:n tblPeopleLocations
    tblLocationTypes 1:n tblPeopleLocations
    tblCityState 1:n tblPeopleLocations

    hth


    "Brian" <Brian@discussions.microsoft.com> wrote in message
    news:7EEFFE8A-4A79-4911-9BBE-6DD58FCB2C25@microsoft.com...
    > I use a City-State reference table in my genealogy database for three

    fields
    > in another table - the birth, death, and primary residence locations for

    each
    > person.
    >
    > The way I originally defined the relationships in the Relationships Window
    > was by using the Show Table option and inserting the table three times.
    > Access shows the tables as City-State_1, City-State_2, City-State_3. Is

    there
    > a better way to establish the relationships?
    >
    > I miss being able to expand the records in my original table with the "+"
    > button. Evidently setting up the relationships as I did does not allow to
    > view related records anymore without a query being performed.
     
  4. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Hi Brian,

    Your relationships sound fine to me. This is exactly how it is done.

    The "+" button that you refer to is known as a SubDatasheet. This is a
    feature that was added with Access 2000. Each table can have one table or
    query specified in the "Subdatasheet Name" property of the table. To see this
    property, open a parent table in design view. Then click on View >
    Properties. You should be able to write a query that includes the three
    fields (birth, death, and primary residence locations) for each record.
    Include the primary key field from this table. Then specify this query in the
    Subdatasheet Name property for the CityState table.

    There is a reason that you may want to temper your excitement for
    Subdatasheets. If you access data over a network (ie. linked tables, as in a
    multiuser application), then you'll want to set this property to [None] for
    all tables:

    BUG: Slow performance on linked tables in Access 2002 and Office Access 2003
    http://support.microsoft.com/?id=275085


    Tom

    http://www.access.qbuilt.com/html/expert_contributors.html
    __________________________________________

    "Brian" wrote:

    I use a City-State reference table in my genealogy database for three fields
    in another table - the birth, death, and primary residence locations for each
    person.

    The way I originally defined the relationships in the Relationships Window
    was by using the Show Table option and inserting the table three times.
    Access shows the tables as City-State_1, City-State_2, City-State_3. Is there
    a better way to establish the relationships?

    I miss being able to expand the records in my original table with the "+"
    button. Evidently setting up the relationships as I did does not allow to
    view related records anymore without a query being performed.
     
  5. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Mon, 31 Oct 2005 17:51:01 -0800, "Brian"
    <Brian@discussions.microsoft.com> wrote:

    >I use a City-State reference table in my genealogy database for three fields
    >in another table - the birth, death, and primary residence locations for each
    >person.


    Heh.

    For my entry, this would have: Shanghai, China; (Null); Shanghai,
    Kashing, Rocky Mount NC, Kobe Japan, Tulsa OK, Sallisaw OK,
    Bentonville AR, East Lansing MI, Berkeley CA, Cambridge MA, Ann Arbor
    MI, Stockbridge MI, Parma ID... and very likely more in the future.

    What's a *primary* residence these days!?

    >The way I originally defined the relationships in the Relationships Window
    >was by using the Show Table option and inserting the table three times.
    >Access shows the tables as City-State_1, City-State_2, City-State_3. Is there
    >a better way to establish the relationships?


    What's wrong with that? It's just letting you define three different
    relationships to the same table. It's not creating new tables of
    course, just table icons.

    >I miss being able to expand the records in my original table with the "+"
    >button. Evidently setting up the relationships as I did does not allow to
    >view related records anymore without a query being performed.


    Table datasheets are of VERY limited utility. What would you want to
    see? Three subdatasheets under each record? How would you want them
    arranged...???

    Use a Form with three combo boxes, or a Query joining the three
    instances of the city table.

    John W. Vinson[MVP]
     

Share This Page