none
Duplicating Master Record with multiple child tables RRS feed

  • Question

  • I need to duplicate a master record along with the child records associated with it.  It needs a new tbl_ClubFitting ID and then new links to the tbl_MemberClub, but same data. same with new tbl_ClubOrder data. The memberID, paymentID and club type and brand Ids would remain the same.

    This is the base query

    SELECT tbl_ClubFitting.*, tbl_MemberClub.*, tbl_ClubOrder.*, tbl_ClubType.*, tbl_ClubBrand.*
    FROM tbl_ClubBrand RIGHT JOIN (tbl_ClubType RIGHT JOIN (tbl_ClubOrder RIGHT JOIN ((tbl_Payment RIGHT JOIN (tbl_Members RIGHT JOIN tbl_ClubFitting ON tbl_Members.ID = tbl_ClubFitting.MemberID) ON tbl_Payment.ID = tbl_ClubFitting.PaymentID) LEFT JOIN tbl_MemberClub ON tbl_ClubFitting.ID = tbl_MemberClub.ClubFittingID) ON tbl_ClubOrder.ID = tbl_MemberClub.ClubOrderID) ON tbl_ClubType.ID = tbl_MemberClub.ClubTypeID) ON tbl_ClubBrand.ID = tbl_MemberClub.ClubBrandID;

    I'm trying to do this with recordsets, loops and code, but am I overthinking it?  Is there an easier way in access?


    • Edited by JHarding08 Tuesday, November 19, 2019 3:00 AM
    Tuesday, November 19, 2019 2:58 AM

Answers

  • This should be the only fields/tables needed to duplicate. Would just need to update the foreign keys as the tables are updated.

    SELECT tbl_ClubFitting.*, tbl_MemberClub.*, tbl_ClubOrder.*
    FROM tbl_ClubBrand RIGHT JOIN (tbl_ClubType RIGHT JOIN (tbl_ClubOrder RIGHT JOIN ((tbl_Payment RIGHT JOIN (tbl_Members RIGHT JOIN tbl_ClubFitting ON tbl_Members.ID = tbl_ClubFitting.MemberID) ON tbl_Payment.ID = tbl_ClubFitting.PaymentID) LEFT JOIN tbl_MemberClub ON tbl_ClubFitting.ID = tbl_MemberClub.ClubFittingID) ON tbl_ClubOrder.ID = tbl_MemberClub.ClubOrderID) ON tbl_ClubType.ID = tbl_MemberClub.ClubTypeID) ON tbl_ClubBrand.ID = tbl_MemberClub.ClubBrandID;

    • Marked as answer by JHarding08 Monday, December 9, 2019 11:20 PM
    Tuesday, November 19, 2019 3:27 AM