1 Reply Latest reply on Nov 23, 2013 9:45 PM by philmodjunk

    Self Joining Relationship



      Self Joining Relationship


           I have a database I'm making that often has the same customer info that gets entered multiple times.  I've noticed every so often the customer will enter in a different email address than before, or they will enter in their AIA number in the AIA field on one occurrence and not enter in a license number in the license number field and on the second occurrence they will do it vise versa.  I know I can add an email address two field or an address two field and from what I understand I can some how use a self joining relationship to have the second email address post to the address two field or add the license number to the to the customers license number field in they didn't enter it in on their prior record.  Each customer currently has these fields:  Name, From Email Address, Option 1 Value (which equals their AIA number), Option 2 Value (which is their state registration number), course name, course number, and address.  Can someone help?

        • 1. Re: Self Joining Relationship

               Wouldn't it make more sense to simply link to this data instead of entering it all over again each time. I'm making a wild guess here, but this sounds like the type of situation where you have an invoicing database with multiple invoices for the same customer. Typically, you don't have a self join relationship, instead, you link between two different tables, each with a different role in the function of your database:


               Customers::__pkCustomerID = Invoices::_fkCustomerID

               The contact information you describe would be stored in a single record in Customers. When starting a new invoice for a repeat customer, you create a new invoice record linked to the same customer record and thus, any needed contact info is looked up from the customers table. Only any changes in the contact data would be entered and saved back to the customers record.