UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Multiple Option Query On Many-to-many Records, Access 2010    
 
   
raykor
post Sep 13 2017, 01:31 PM
Post#1



Posts: 135
Joined: 5-April 09
From: Seattle, WA


A simplified mock-up of my problem: I have some "Object" records and some "Feature" records. Each Object can have multiple Features and each Feature can be used to describe many different Objects. Thus the many-to-many relationship.

See attached diagram in case that wasn't clear.

I am trying to create a query that returns all Objects (just once) that have a particular combination of Features. For example, all Objects that have a "link" record with the same set of 3 FeatureIDs. Another example of this concept: an online store where you select multiple features and each selected feature further filters the list of displayed products.

Thanks.


For the time being, I would like to avoid a discussion of WHY the tables are designed this way and focus on whether or not it is possible to achieve what I need in a query. (I know i can solve it programmatically but was hoping for a query.) If it turns out that there is no query that will work with this table design, then I will explain in more detail why it is designed this way and perhaps an alternative design could be suggested.
Attached File(s)
Attached File  111.jpg ( 61.97K )Number of downloads: 1
 
Go to the top of the page
 
ADezii
post Sep 13 2017, 01:49 PM
Post#2



Posts: 1,780
Joined: 4-February 07
From: USA, Florida, Delray Beach


How are you defining how many, and what specific features Objects have in common? Example using MANY <==> MANY Relationship:
CODE
What Doctors see Patients John Doe, Mary Kay, and Mark Bowman?

This post has been edited by ADezii: Sep 13 2017, 01:50 PM
Go to the top of the page
 
raykor
post Sep 13 2017, 02:02 PM
Post#3



Posts: 135
Joined: 5-April 09
From: Seattle, WA


I apologize but I don't understand your question.

Here is another example that MAY make my problem easier to understand:

Let's say I have a table of Vehicle Models. I have another table with Colors.

Each Model is offered in several Colors and so I cannot simply add a ColorID field to my Vehicle Models table; I have to use a many-to-many middle table to store the multiple Colors that each Model is available in.

A customer walks in and says that they only buy cars in red or black. I now need to pull up a list of all Vehicle Models that are offered in BOTH red and black. (The fact that a Model may be offered in more colors than just those two is irrelevant.)
Go to the top of the page
 
raykor
post Sep 13 2017, 02:07 PM
Post#4



Posts: 135
Joined: 5-April 09
From: Seattle, WA


Let me add: the query is being created in VBA. It doesn't matter how, you can simply assume that my code will have available a list of the user selected FeatureIDs (or ColorIDs in my second example) that the query will need to use to filter the table of Objects (or Vehicle Models).
Go to the top of the page
 
raykor
post Sep 13 2017, 02:23 PM
Post#5



Posts: 135
Joined: 5-April 09
From: Seattle, WA


Maybe a UNION query?

Something like:

CODE
SELECT OBJECTS.ObjectID, OBJECTS.ObjectName FROM OBJECTS INNER JOIN ObjectFeatureLinks ON OBJECTS.ObjectID = ObjectFeatureLinks.ObjectID WHERE (ObjectFeatureLinks.FeatureID=16)
UNION
SELECT OBJECTS.ObjectID, OBJECTS.ObjectName FROM OBJECTS INNER JOIN ObjectFeatureLinks ON OBJECTS.ObjectID = ObjectFeatureLinks.ObjectID WHERE (ObjectFeatureLinks.FeatureID=41)
Go to the top of the page
 
raykor
post Sep 13 2017, 02:43 PM
Post#6



Posts: 135
Joined: 5-April 09
From: Seattle, WA


The UNION query did not work but I solved it with a GROUP BY query where I also added a condition to count the ObjectIDs and filter to show only those Objects that have the same count as the number of Features that my query is filtering by. Since this query is created in VBA, it is easy for me to add these values to the assembled query string.

CODE
SELECT OBJECTS.ObjectID, OBJECTS.ObjectName
FROM OBJECTS INNER JOIN ObjectFeatureLinks ON OBJECTS.ObjectID = ObjectFeatureLinks.ObjectID
WHERE (ObjectFeatureLinks.FeatureID=16) OR (ObjectFeatureLinks.FeatureID=41)
GROUP BY OBJECTS.ObjectID, OBJECTS.ObjectName
HAVING (Count(OBJECTS.ObjectID)=2)
Go to the top of the page
 
raykor
post Sep 13 2017, 02:46 PM
Post#7



Posts: 135
Joined: 5-April 09
From: Seattle, WA


Minor refinement: changed the multiple OR clause to an IN clause.

CODE
SELECT OBJECTS.ObjectID, OBJECTS.ObjectName
FROM OBJECTS INNER JOIN ObjectFeatureLinks ON OBJECTS.ObjectID = ObjectFeatureLinks.ObjectID
WHERE ObjectFeatureLinks.FeatureID IN (16,41)
GROUP BY OBJECTS.ObjectID, OBJECTS.ObjectName
HAVING (Count(OBJECTS.ObjectID)=2)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th September 2017 - 05:38 PM