Query Criteria from another table?

[gmontano]

I have a table of users and I have a table of types of work.



I also have a query that pulls in the work.

I am stuck trying to figure out how I can further filter the query to only pull in a piece of work of the user can work that type of work.

I do not know how to show that a specific user can work different items and have the query see it is that user and which items he can work, so only those ones are displayed.

Below, Billy can work Refunds, Payments, and Letters. Jane can only do payments. How can I filter the work to only show what Billy can do if he is the one in the form.

Example:

User - Work
Billy - Refunds, Payments, Letters
Jane - Payments


Some more info, the form pulls in one piece of work at a time. It automatically gets their userid and such. If I could have the query see that it is Billy and only show the the three types of work he can do, I would be done.

This is driving me nuts, so any help would be appreciated. I am not sure even how to set up a table for this. I have tried so many things, it is crazy.

Thank you.

[jzwp11]

It sounds like you need another table to associate the person with the types of work they can perform. Since a person can do many types of work that describes a one-to-many relationship. Additionally, since a type of work can apply to many people that also describes a one-to-many relationship. When you have 2 one-to-many relationships between the same two tables, you represent that with a junction table like this:

tblPeopleWork
-pkPeopleWorkID primary key, autonumber
-fkPeopleID foreign key to your users table
-fkWorkID foreign key to your work type table

In terms of forms, you would have a main form based on your user table and a subform based on the junction table. You would use a combo box in the subform to populate the work type. This set up would show all work that can be performed by each user.

Alternatively, you could have your main form based on the work type table and then the subform based on the junction table (the user would be populated using a combo box). This set up would show which users can do each type of work.