many-to-many relationship problems

[ryan_eng]

Hi all, I'll try and explain this as best I can to make it easy on you. My
database is for keeping track of documents, drawings and action items all
relating to tasks and employees.

Tables:
1. Resources - Res_ID (pk), Res_fname, Res_lname (the employees table)
2. Drawings - dwg_ID (pk), Resource_ID, Task_ID and various other fields
related to the drawing not affecting relationships.
3. Documents - doc_ID (pk), Resource_ID, Task_ID and various other fields...
4. Actions - action_ID (pk), Task_ID and other fields. This table is linked
to the Resource table through another table with only Resource_ID and
Action_ID in it to work with the many-to-many relationship.
5. Tasks - Task_ID (pk) and other fields....This table is linked to the
resource table in the same fashion as the actions table is. This is where my
problem lies.

Since employees are assigned to the action items, I used a subform in the
action item form for enterring the multiple employees and storing that data
in the linking table, no problem. For the Tasks - Resource link, I will not
be directly assigning employees to the tasks. The relationship is dictated by
what drawings, documents and action items the employees are working on (thus
the Task_ID field showing up in all of those tables). SO, for the Task form,
I simply want to display what employees are working on the Task in question.
The problem is that because I am not directly enterring data into the linking
table, there is nothing to display.

So far I have been able to set up 3 queries that list:
1. Employee, Doc_ID, Task_ID
2. Employee, Dwg_ID, Task_ID
3. Employee, Action_ID, Task_ID

I think this is one step closer to displaying the right data, but the
linking table will still be empty, which I guess implies that it is useless.
This is where I am stuck. How do I put together a 4th query that lists the
task ID and the various employees working on it? There will be duplicates
since one employee can be working on multiple documents / drawings / action
items and I don't want them to show.

I hope that is enough info, please let me know if anything else is in
question.

Thanks
RYAN

 

[Sharkbyte]

Ryan:

Your fourth query should be using the first 3 queries as source data.
Create a relationship, for all, using Task_ID. Then you can create one query
displaying Employee, Doc_ID, DWG_ID, Action_ID, Task_ID. Then you can use
Group By to remove duplicate records.

HTH

Sharkbyte

 

[ryan_eng]

Thanks for the input Sharbyte, but I'm still having trouble.

Can you tell me how to make this relationship? Do I need to use my linking
table that has the Res_ID and Task_ID fields?

Thanks
Ryan

 

[Sharkbyte]

In the query window, where you have your 3 source objects, simply
drag-and-drop Task_ID from one object to Task_ID in the next. The query then
creates a relationship (only within the new query) using those fields. You
can now build your new query using this temporary relationship.

Sharkbyte

 

[John Nurick]

Hi Ryan,

If I understand your description right, you don't need and shouldn't
have a special table to implement a many-to-many relationship between
Resources and Tasks. This is because you already have two such
relationships, via Drawings and Documents respectively, plus a third,
less direct, one via Actions and the ResourcesActions joining table.

To get a list of all Resources working on a particular Task you need a
union query that combines the output of the three queries you already
have (I'll call them qryEmpDocTask, qryEmpDwgTask, qryEmpActionTask),
something like this:

SELECT Employee, Task_ID FROM qryEmpDocTask
UNION
SELECT Employee, Task_ID FROM qryEmpDwgTask
UNION
SELECT Employee, Task_ID FROM qryEmpActionTask
;

and use it as the data source for a subform or whatever.

 

[ryan_eng]

John,

How do I make that Query in design view?

Ryan

 

[ryan_eng]

Sharkbyte,

I had made the relationship that way before and it didn't work, so it must
be something wrong with the rest of the query.
Which Res_ID would I use? Does it matter which query it comes from? Do I
have to include the Resource table in the query so that I can make an
expression which displays the full name? (ie. Resource: [Res_fname]&"
"&[Res_lname])
Also, I'm confused on how to use the "group by" function, can you explain?

Sorry for all the questions, but I still don't have a great understanding of
queries...

Ryan

 

[John Nurick]

You can't. Union queries have to be typed in SQL view.