Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2002
    Location
    Melbourne
    Posts
    92

    Unanswered: importing to a one to many relationship

    Ive imported a table from an old DB that has some data that is useful. This is my situation.

    the old table has the following fields that need to be split into to two tables that are linked e.g

    tblreferrals(old data) to be split in to 2 table
    ClientID
    MPMSNO
    Surname
    GivenName
    DIMIA Address
    DateIn
    DateOut


    In need theSurname, GivenName and MPMS in the Clients tables and the rest of the data linked to my Tenant_Property_booking table which has Address, dateIn and DateOut. I need to data to match up. I know how the copy form one table to another but not when the needs to be data in the many side.

    Do I need to do an update query and, if so, how do I do this. I can I do this. Ive been dieing work this out for a long time.
    Attached Files Attached Files

  2. #2
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    INSERT INTO tblTenants ( GivenName, LastName, MPMS )
    SELECT tblreferrals.[Given Name], tblreferrals.[PAFamily Name], tblreferrals.MPMSNO
    FROM tblreferrals;


    INSERT INTO tblTenant_Property_Bookings ( DateOut, DateIn, TenantID )
    SELECT tblreferrals.DateOut, tblreferrals.DateIn, tblTenants.TenantID
    FROM tblreferrals INNER JOIN tblTenants ON (tblreferrals.[PAFamily Name] = tblTenants.LastName) AND (tblreferrals.[Given Name] = tblTenants.GivenName) AND (tblreferrals.MPMSNO = tblTenants.MPMS);


    SELECT tblTenants.TenantID, tblTenants.GivenName, tblTenants.LastName, tblTenants.MPMS, tblTenant_Property_Bookings.BookingID, tblTenant_Property_Bookings.PropertyID, tblTenant_Property_Bookings.DateIn, tblTenant_Property_Bookings.DateOut, tblTenant_Property_Bookings.ExpectedExitDate, tblTenant_Property_Bookings.[4WeekLetter]
    FROM tblTenants INNER JOIN tblTenant_Property_Bookings ON tblTenants.TenantID = tblTenant_Property_Bookings.TenantID;

  3. #3
    Join Date
    Nov 2002
    Location
    Melbourne
    Posts
    92
    Thnaks for that!!! wow but how do i implement it?

  4. #4
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    run query 1 then 2
    Attached Files Attached Files

  5. #5
    Join Date
    Nov 2002
    Location
    Melbourne
    Posts
    92
    Worked like a charm!!!!!!!!!!!!!!!

    thanks heaps dude


    very much indebted to you!

    :-)

  6. #6
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    No problem - gotta do something during the commercials

Posting Permissions

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