Select/Filter a one to many link

[HSXWillH]

In Access 2007, I have an issue that I've tried attacking from different angles, but have not gotten the correct results.

I have two tables, tblBase and tblAttributes with a common Key of Stock_ID. In tblBase, I have a Description of the item, but the more detailed attributes are handled in tblAttributes with field names Attribute_Type and Attribute_Value.

Sample Data is:
Stock_ID -- Attr_Type -- Attr_Value
00001 -- Brand -- Finest
00001 -- Year -- 2010
00002 -- Brand -- Finest
00002 -- Year -- 2009
00003 -- Player -- Thomas
00003 -- Player -- Stevens
00003 -- Brand -- Bowman ....

I have identified 18 different Attribute_Types that could show up for each Stock_ID but most of the stock items will touch on only 5 or 6 of these. However there are a few Types that could contain more than 1 Value such as 00003-Player-... above.

What I wish to do is filter down to a listing of Stock Items that contain user-selected options based on the attributes chosen via combo-boxes or other form inputs. For instance, if I choose Brand - Finest only, it would give me 00001, 00002...but if I choose Brand - Finest and Year - 2009, it would only give me 00002.

However, I am unsure of how to do this. I have thought about a crosstab query but in Access, the Value field will not work when I have a situation with one item have multiple players involved. I thought about a standard filter, but I get a situation where if I select an "AND", it will not work as each record is only 1 attribute. The "OR" gives me every line that matches any criteria chosed.

I tried to minimize empty cells in records with the original plotting of this database while still retaining standard data normalization, but I'm not sure of how to proceed? Thoughts that I might not be considering and thank you in advance.

[TheSmileyCoder]

Exactly how are you going to use this?

User(s) select from a form several limiting factors, and you want to show the first match/all matches?

Maybe some of our SQL gurus can cook up something smarter, but my approach would probably be to use VBA to create and save a querydef after the update of each criteria, and have the 2nd query query from the first query.

So user selects criteria 1, and code creates Qry_MatchesCrit1, and user then selects criteria 2, and code created qry_MatchesCrit2 which queries FROM qry_MatchesCrit1. You should be able to do all of this dynamically.

[HSXWillH]

That was a 3rd thought I had; but I wasn't sure if I could in theory have 18+ nested querydefs since that's feasible as a drill-down.

My end goal is I want to use every record that matches the final listing and allow the user to add it to a wishlist/order form so limiting the results to just what is targeted is key.

[Rabbit]

I'm thinking you could do a dynamic query that would do something like this:

SELECT id
FROM someTable
WHERE 
   (field1 = something AND field2 = something) OR
   (field1 = otherthing AND field2 = otherthing) OR
   (field1 = thirdthing AND field2 = thirdthing)
GROUP BY id
HAVING COUNT(*) >= 3

This will return all ids that have those attributes matching. Any id with less than those 3 attributes would not get returned.

A potential issue is that ORs can be really slow and you may have to use an equivalent UNION query instead.

[HSXWillH]

Rabbit, sorry for the delay in responding. I went ahead with your idea as a solution for now becuase I hadn't thought about the HAVING option. That's exactly what I was aiming for and until I run into speed concerns, it's what I will go with. Thank you.

[Rabbit]

Not a problem HSX. Good luck with the rest of your project.