Relational Form Stupidity

[WANNABE]

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.

 

[WANNABE]

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

 

[Jeff Boyce]

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

 

[WANNABE]

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

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.

 

[John Vinson]

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]