AnsweredAssumed Answered

many to many self join?

Question asked by PennyWilliamson on Oct 27, 2010
Latest reply on Nov 9, 2010 by philmodjunk


many to many self join?


Help!  I've looked at this for hours and can't figure it out.   I am rewriting a FMP3 database that manages employees' shifts.

Each employee will bid a shift that is based on a start time and desk, each desk has two start times in a day - example desk 1 has a shift for 1 person to bid that starts at 0430-1230 and desk 1 has a shift for 1 person to bid that starts at 1230-2030(this is a 24-hour operation that requires a body at every desk for at least 16 hours of the day).  After all employees have bid the shift they want to work for a 3 month period, I have a script that builds a record in a seperate database(called Exceptions) for each shift, each day for the duration of the 3 month period -- all good up to here.  All records have a Serial ID.   For the duration of the shift bid employees can change their shifts with other employees -- they can change part of their shift --- John works Sally's full shift or they work part of their shift - John works his shift from 0430-0830 and Sally works the second half of the shift 0830-1230.  There can be as many as 3 people working 1 shift.  1 person can work 'many' shifts in a day and 1 shift can have 'many' employees working in a day.  The employees need to be able to make these changes themselves so it has to be easy for them to manuever.  

I have:

Employee  table - employee's personal information

Shift bid table - 60 shifts available for employees to select the shift they want

Exceptions table - A record created for each employee, each day with the shift they have bid - ExcSerialID is the Primary Key

I currently have a self join in Exceptions with ExcSerialID to FExcSerial ID.  When an employee selects a person to work for, a new record is created with ExcSerialID and I set the FExcSerialID to the parent's ID, so I know the parent/child records.  If I set the match fields up for name = gname, date = gdate then the employee can change gname and go to another persons record without a problem(via portal).  If I set a match with the ID's then and try to use the gname/gate to select what I want I get the first match to that name -- not the name/date match. 

In the past I have worked with the concatenated matches with ease have been able to make just about anything work but I am trying to do this 'the right way' with the SerialID's and I can't get my brain to think like this.  I know I am missing some structural piece and I think it is a join table, but I have not worked with a join table and serialID's before.  The FMP3 databases used a join table but not with SerialID's --- it set things as it needed to for viewing.

Thanks for any guidance you can give.