5 Replies Latest reply on Aug 7, 2009 6:03 PM by RandyS

    Relationships:  I just don't get it.



      Relationships:  I just don't get it.


      I've been trying to make a database to track events and employee scheduling at my theater.  I just can't get things to work right.  I don't know what I'm doing wrong.  :(


      Here's a small sample of what I'm trying to accomplish:




      In table 1 ("Events") we enter information about the events that are going to be presented:

          Event ID No.




      In table 2 ("Employees") we enter the names of the employees who will be staffing these events.

          Employee ID No.




      In the third table (the join table) we are supposed to bring the events and employees together so we can schedule people to run the shows.

      I have the part where the event links in to the join table working but I can not get the link between the join table and employees working.


      What am I missing?  I've been banging my head against this for two weeks.  I just don't know what to do.

      Any help?




      Randy S. 

        • 1. Re: Relationships:  I just don't get it.

          Do you have the following relationships?


          Events::Event ID No = Jointable::Event ID No

          Employees::Employee ID No = Jointable::Employee ID No


          Everytime you want to assign an employee to an event, you'll need to create a new record in the join table with the correct matching envent and employee numbers. This can easily be done with a portal based on the Join table.

          • 2. Re: Relationships:  I just don't get it.
               There are a number of things that you have "upside down", so to speak. Instead of trying to list them all I suggest you look at the demo here.
            • 3. Re: Relationships:  I just don't get it.

              Okay... So I think I get it.


              The tables were pretty much right and the relationships were right.  It was the way I was looking at them that was creating the problem.


              I was in the join table trying to look at both of the other tables when I needed to be in the "Event" table, looking at the "Employee" table THROUGH the join table.


              I made this analogy to somebody else who didn't understand but I'll try it out on you guys.


              You are standing in a hallway, looking at a dozen fishtanks on shelves all along the walls.  Each fish tank can have any number of fish.

              Hallways are "Events".  Fishtanks are records in the join table.  Fish are "Employees."


              If I make my layout using the join table, it is like I am sitting in the fish tank so I can not join fish and events because I can not see into another fish tank.


              If I make my layout using the "Events" table and making a PORTAL to the join table, I will be able to look at ALL the fish tanks and put fish into any tank I want.  When you do it the other way, you can not get to any of the other fish tanks.  You must be standing in the hallway to sort fish into fish tanks.


              Make sense?



              • 4. Re: Relationships:  I just don't get it.

                I don't think your analogy is correct. A fish can be only in one tank, but an employee can participate in many events, thus have many records in the join table.


                Let me suggest a simpler one: events are events, employees are employees, and records in the join table are tickets.

                • 5. Re: Relationships:  I just don't get it.

                  You're right.  That was out there in left field.


                  Anyhow, I think I got it now.




                  Now!  To take this and apply it to the big database!  ;)