Question problem with 1 primary key being used for multiple foreign keys (1 Viewer)

bgadeyne · Jan 6, 2017

Hi all,

I'm relatively new to MS Access and am experiencing following problem:

I've created following objects & relations:

- Tbl 'EmployeeT' which contains:

EmployeeID (autonumber) = primary key
EmployeeName (Text)

- Tbl 'TaskT' which contains (besides TaskID and TaskTitle):

TaskOwnerID (Numeric) = foreign key for primary key 'EmployeeID' from Tbl EmployeeT
TaskAddedByID (Numeric) = foreign key for primary key 'EmployeeID' from Tbl EmployeeT

So an employee can be either (or both) the author or the owner of a task.

Problem: I can't add EmployeeT.EmployeeName twice to a continuous form to show once the name of the TaskOwner and once the name of the person who added the task. I suppose I have to do this by adjusting the SQL code.

Question: How can I get my continuous form to display both the correct Employee name?

Thanks a lot!

CJ_London · Jan 6, 2017

your continuous form should have a recordsource of TaskT, and the two controls would be comboboxes based on EmployeeT.

In relationships, you would have TaskT once and 2 copies of EmployeeT (one will be called EmployeeT_2), then you can create relationships to both fields in TaskT

bgadeyne · Jan 6, 2017

Thanks CJ_London for your quick reply!

I fully agree with your solution, however I prefer using text boxes instead of comboboxes.

Any ideas on that?

Thank you!

Minty · Jan 6, 2017

In your forms underlying datasource simply bring the employees table in twice - once for each value.

CJ_London · Jan 6, 2017

I prefer using text boxes instead of comboboxes.

Any ideas on that?

Sorry, no - just surprised you want users to remember a random number for each employee rather than select a name

bgadeyne · Jan 6, 2017

CJ_London, I think I did not make myself clear in my first post.

I actually want to display the name of the employee in my form (not the EmployeeID). I do not want the users of the form to be able to edit the records (e.g., chosing from a list in the combobox).

CJ_London · Jan 6, 2017

Sorry - I misunderstood.

Solution is the second paragraph of my first post - also suggested by Minty

Question problem with 1 primary key being used for multiple foreign keys (1 Viewer)

bgadeyne

New member

CJ_London

Super Moderator

bgadeyne

New member

Minty

AWF VIP

CJ_London

Super Moderator

bgadeyne

New member

CJ_London

Super Moderator

Similar threads

Users who are viewing this thread