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.
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.