SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    ********* Poet X Q mano's Avatar
    Join Date
    Oct 2002
    Location
    Still Thulcandra
    Posts
    997
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Split table (msaccess)

    Hi,

    I've been asked to help optimize an access database.

    Problem is, they've been double-registering for a long time now.

    It's the database for a sponsorship program, where people in this country can "adopt" a child in the third world and give financial support.

    Well... As of now, everything is stored in one table, but I want to split it up so that the information about the people here is stored in one table and the information about the children in another.

    That way they don't have to double-register if one person supports several children.

    Are there tools to do this effectively, or will they have to do everything manually?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    assuming there's some way an adopter row and a child row are related, yes, it's fairly easy to alter the database

    select the adopter rows into a new table

    then select the child rows into another new table, and make the child row "point to" its adopter row

    then you can have multiple child rows point to an adopter

    rudy

  3. #3
    ********* Poet X Q mano's Avatar
    Join Date
    Oct 2002
    Location
    Still Thulcandra
    Posts
    997
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, as of now, there aren't but obviously, I'd make some sort of adopter_id in the child table...

    But the clue is just to copy it in, is that it?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    use SELECT ... INTO syntax

    just curious, how do you know who's the adopter of a particular child now?

  5. #5
    ********* Poet X Q mano's Avatar
    Join Date
    Oct 2002
    Location
    Still Thulcandra
    Posts
    997
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's all registred in one table... And the info about the adopters are double- triple and even quadrouple-registred... No wonder, they need to update the thing... It's horrible...

    Thanks btw... I can see why they made you member of the month!

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    ok, i think i get it

    you have adopter and child on the same row

    i thought they were on separate rows

    so, to pull the adopters, use DISTINCT

    your main difficulties will be in changing the front end of whatever application screens are used to enter new data, to search for existing adopters and not re-enter them

    good luck


Bookmarks

Posting Permissions

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