Many to Many Queries / Filters 1

[Grounded]

I have data in three tables in a many to many relationship. Right now i am just trying to get a sample database working before i implement it on a larger scale with our current project.

I have people in one table
Colors in another table and a link table between them.
I have a People form with a colors subform.

I have successfully used a multi select list box to build a query that can filter the form based on one color. I can also filter the form based on one color OR another. I can not select two colors and pull all the people who have both colors chosen. In the end i am trying to develop a staff skills and training db. it will require several many to many relationships where staff will be linked to several categories of skills and trainings.

The solution i have been working on uses a separate form with the multi-select list box to build a SQL statment and pass it to the data entry form using the OpenArgs parameter of DoCMD.OpenForm. The data entry form opens, looks to see if there is anything in the openargs and if there is it sets the record source to that passed SQL statement.

I am open to other solutions.

Thanks.

Iz


I know this has been addresses some - like in the following thread:

http://www.tek-tips.com/viewthread.cfm?qid=1146740

couln't get this solution to work.

i got hung up on
sFilter = sFilter & "'" & rs.Fields("IDPeople") & "',

 

[fneily]

I, and I'm sure others, would like to know your tables' structures. You have a many-to-many relationship between People and Colors. There should be a junction table connecting the two to form two one-to-many. The junction table should at least contain the primary keys of the other two tables. If this is set up correctly, you can then query for one person, many colors or one color, many people or whatever you want. The answers just fall out.
You would Not have a People form with Colors as a subform. You would have a People form with the Junction table as the subform (one-to-many). There are some threads that will point you to information on relationships.
Basically the first thing you need to do is:
Normalize the tables
Relate them (you may want to use the Relationship window)
Join them in the queries (inner, left outer, right outer)

 

[Grounded]

you are exactly right.
i have three tables people, colors and a linktable.
the link table stores a linkID and the IDs for people and colors.

the subform is based on the link table not the color table.

i still don't see how to query for all the people that chose red and green. I can do it by building a query for red, a query for green, then a third query to join those two but that is not a feasible solution to build dynamically for many selected values.

 

[PHV]

A starting point:
SELECT peopleID FROM linktable L INNER JOIN colors C ON L.colorID = C.colorID
WHERE C.color IN ('red','green') GROUP BY peopleID HAVING COUNT(*) = 2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Grounded]

this worked well.
i used this sql statement to update a query for each multiselect box.

then i built a separate sql statement on the fly that created series of inner joins - joining the appropriate queries (depending on which listboxes have selections) to the original recordsource for the form.

this is second sql is stored in a global variable and when the form with the data loads it checks that variable. if there is a sql statement there then the it filters its record source using it.

works great.