 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / August 2006Relational Form Stupidity
please educate me.. I have 2 simple tables employee, tasks. I have a form for tasks, that I want to automatically identify the employee using environ("username") and is a matching record exists in the employee table then pass the EID to the task form and save it with the task record. I have created a qry that works ( SELECT userid, empid FROM emp WHERE userid=environ("username");) and tried to pass that to the tasks.empid field through the fields default value property, with something like this (=[qry_SelUser]![empid]), and I also tried putting the query directly in the default value nothing has worked so far. Can someone please clue me in... Thank you. I may have figured it out but I welcome any ideas on how to do it better.... I've created a sub form on the employee form linking it on empid, and using the qry I had created below as the source for the employee form. ============================================================== > please educate me.. > [quoted text clipped - 9 lines] > worked so far. Can someone please clue me in... > Thank you. If you only have two tables (Employees, Tasks), then I have to assume that each task can have only one Employee assigned (or each employee can only do one Task), and that you do not care to keep a history (since the next task assigned to an Employee would wipe out the previous Task). I suspect you have a many-to-many relationship between employees and tasks, and that you DO wish to keep a history (who was working on what when). If this is true, you'll need a third table to resolve the many-to-many situation. This third table holds, for lack of a better description, TaskAssignments (i.e., Employee X Task). Note that this approach also lets you assign more than one employee to a given task, and lets an employee be assigned more than one task at a time. Regards Jeff Boyce Microsoft Office/Access MVP > please educate me.. > [quoted text clipped - 9 lines] > worked so far. Can someone please clue me in... > Thank you. This is a one to many relationship (1 employee per task) with multiple tasks per employee. But I do like the many to many idea. It has been many years since I last worked with a many-to-many relational table. Do I remember correctly that it only needs 2 fields the Employee ID and Task ID? is there any need for a primary key field? Thanks. > If you only have two tables (Employees, Tasks), then I have to assume that > each task can have only one Employee assigned (or each employee can only [quoted text clipped - 28 lines] >> worked so far. Can someone please clue me in... >> Thank you. >This is a one to many relationship (1 employee per task) with multiple tasks >per employee. But I do like the many to many idea. It has been many years >since I last worked with a many-to-many relational table. Do I remember >correctly that it only needs 2 fields the Employee ID and Task ID? is there >any need for a primary key field? Thanks. Those two fields make a good primary key. Just ctrl-click both of them in table design view and click the Key icon. This will allow multiple employees to perform the same task, or vice versa, but prevent assigning the same task twice to the same employee. (If you want to do so you need a third field such as the date the task was assigned). John W. Vinson[MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.