Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    7

    Unanswered: One field, two records to two fields, one record

    This is much trickier than I would have thought.

    In Access 2003 I have a table with these columns:

    IDNumber Name Title1 Title2

    currently the data looks like this:


    IDNumber Name Title1 Title2

    12 Jennifer Lopez Actress
    12 Jennifer Lopez Singer
    13 Other Person Other
    14 Someone Else Job

    But I don't want there to be duplicates, so I want the data to look like this:

    IDNumber Name Title1 Title2

    12 Jennifer Lopez Actress Singer
    13 Other Person Other
    14 Someone Else Job


    It's not a problem for me to get a list with only the duplicates, and it's not a problem for me to delete the duplicate record. What I am having trouble doing is getting the second Title1 into Title2. Or the first one--it doesn't matter.

    I'm trying to do this with SQL, since I would prefer if someone besides me could understand it when I leave.

    I would be very grateful if anyone had any ideas.

    Thanks.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    what if someone has 3 titles? How about 4? How about 1? Welcome to relational database theory 101...



    For the greatest flexibility, you may want to consider how you track individuals, and occupations for a given individual. Here's a couple ways to do that:


    Two tables, one for people and one for occupations....

    tblPeople
    -------------
    people_id
    first
    last

    tblOccupations
    ---------
    occupations_id
    people_id
    description


    You could get REALLY crazy and create a third table for occupation types. In that case you would scrap the above mentioned table and replace it with:

    tblOccupations
    --------
    occupation_id
    description

    tblOccupation_detail
    ---------
    occupation_detail_id
    occupation_id
    people_id
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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