By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,526 Members | 2,189 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,526 IT Pros & Developers. It's quick & easy.

Query results for M:M relationships

P: 1
Hi All,

I'm working in Access 2000. Sorry for the long post, I'm trying to be as clear as possible.

I've created junction tables to express M:M relationships for contractors and job task. Each contractor can performs many tasks and a task can involve many contractors. The general structure is:

tblContractors
ContID (pk)
Fname
Lname
Phone, etc

tblTasks
TaskID (pk)
TaskDescription

trelContractor_Tasks
ContTaskID (pk)
ContID (fk)
TaskID (fk)

tblSite
SiteID (pk)
County
ContID (fk)

There are other tables that concern tools, materials, etc.

In a main form based on tblSite, a user will select a contractor and multiple tasks. They select the multiple task via a continuous subform. On the main form, there are also some comboboxes for the user to select the type of tools and materials they used.

The question I want to ask in a query is "At a given site, what tasks were completed and what tools and materials were required?"

A query with results may look like this:

[SiteID], [TaskID], [ToolID], [MaterialID]
2________4_______45_______ 7
2________5_______45_______7
4________1_______3 _______65

If you look at the results, it looks like SiteID #2 required ToolID #45 and
MaterialID #7 two times.

Even though it looks like TaskID's are linked with tools and materials, they are not. Really, what this means is at site #2, task 4 and 5 were completed and tool 45 and material 7 was used in some capacity.

I understand that tools and materials should be linked to given tasks but it is not practical for a user to do this....one task may require lots of tools and materials and a tool may be used in lots of tasks. I don't want the user spending all day trying to enter data. Rather, I want the user of the form to simply select all the tasks they did and what tools and materials they used. The only link between these is the SiteID field.

I want to run a query that does not appear to duplicate toolID and MaterialID.
Even though I know Access doesn't return records this way, here is a hypothetical result of what I am trying to express through a query:

[SiteID], [TaskID], [ToolID], [MaterialID]
2________4 AND 5____45______ 7
4__________1________ 3 _____ 65

I realize I may be asking the impossible, and my problem is based on flawed tbl/form design, but any help is appreciated. Thanks very much.
Apr 17 '07 #1
Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,355
Looks like you need to drop task_id from the query and select unique.
Apr 17 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.