I am putting together a database of All departments in a hospital and our technicians responsible for them. I also need to have 2 backup techs. The database for the techs will be them same techs as the backups. Right now I created tables, fields & relationships for each individual techs and backup techs and that seems like not a very good idea since it is all the same.

 

I need suggestions of how I can pull this of easily especially for doing tech and backup tech updates. I have attached relationship screen and the database file.

 

Thanks,

Kevin

Biomed Techs Area Responsibilities.fmp12

Didn't we discuss this already?
https://fmforums.com/topic/104920-technicians-departments-responsibilities/?tab=comments#comment-475077

 

Yes and no! Same project different question. I have assigned Id's to everything and have it part of it working but had to add more backup techs. Rather then duplicate techs and backup techs I was looking for input on how it should be in a relation.

 

Most Forums get upset when you ask other questions within the original post.

I think it's the same question. It's all about how to handle a many-to-many relationship. And the answer is the same: you need a table of Departments, one table for the technicians and a join table for the assignments of technicians to departments.

Have a look at the attached demo. It shows a textbook implementation of a many-to-many relationship. Note that it is symmetrical: you can assign staff to a department or departments to a staff member, with the end result being the same. 

Assignments.fmp12

I tried the example and great learning experience. I was going about it all wrong. But hey at least I tried!

 

Thanks for the example!

Kevin