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

Select/Filter a one to many link

P: 57
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.
Sep 20 '12 #1
Share this Question
Share on Google+
5 Replies

Expert Mod 100+
P: 2,321
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.
Sep 20 '12 #2

P: 57
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.
Sep 20 '12 #3

Expert Mod 10K+
P: 12,366
I'm thinking you could do a dynamic query that would do something like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT id
  2. FROM someTable
  3. WHERE 
  4.    (field1 = something AND field2 = something) OR
  5.    (field1 = otherthing AND field2 = otherthing) OR
  6.    (field1 = thirdthing AND field2 = thirdthing)
  7. GROUP BY id
  8. 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.
Sep 20 '12 #4

P: 57
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.
Sep 24 '12 #5

Expert Mod 10K+
P: 12,366
Not a problem HSX. Good luck with the rest of your project.
Sep 24 '12 #6

Post your reply

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