Multiple Selection Query with Many to Many Relationship

[by32]

I have a database that has several many to many relationships between tables using junction tables. I am trying to build a SQL query through VBA that will take search criteria from a form and then open a report based on that search criteria. While I am able to set that up, I realized that there were some issues with the queries I had set up.

• If I have the following junction table, and I have a multi-select combo box with values 1,2,3 selected, I need to have a query (or code) that looks through this table and returns the ID that has values 1,2, and 3. I can't think of a way to do this through SQL or VBA.

ID | Value
1 1
1 2
1 3

Obviously I can't use the AND operator because no one record will have all three values, and if I use the OR operator, it will pull any record that has a value of 1 or 2 or 3.

I've tried a bunch of different things but none of them have been a good solution, so I'm struggling big time with this. If anyone has ever had to do something similar or worked extensively with many to many relationships and queries in Access, I would really appreciate the help.

I can provide some more specifics if that would help, let me know.

Thanks in advance...

 

[Mr. B]

I assume you are already retrieving the selected records from the list box, try building an In(1,2,3,) statement and use that in your sql statment as the criteria for your field.

 

[by32]

Mr. B,

I guess I should have added that to my original thread...I did try the SELECT IN SQL you mention...That definitely has worked well for me, but the problem with using that in my situation is that I need the query to return an ID that has an exact match. With this table:

ID | Value
1 1
1 2


If the selections made in the combo box are Values 1,2 and 3, then the query would still return ID 1, even though it doesn't have Values 1,2 and 3.

I'm probably doing a terrible job of explaining this, but I hope this helps.

Thanks