Querying a data set for a complete list

[jason majchszak]

Ok i have an issue I have not been able to solve. I have a list of items that i want to query against a table that has all item-store combinations that exist. The problem is that I need the query to return only the stores that have all the items in my list. So if I had 20 items in my list the query result would return only the stores that carry all 20 items. It sounded easy enough, but I can't figure this one out. Does anybody know if this is possible in access?? Thanks in advance!!
J

 

[Bob Barrows]

Ok i have an issue I have not been able to solve. I have a list of
items that i want to query against a table that has all item-store
combinations that exist. The problem is that I need the query to
return only the stores that have all the items in my list. So if I
had 20 items in my list the query result would return only the stores
that carry all 20 items. It sounded easy enough, but I can't figure
this one out. Does anybody know if this is possible in access??
Thanks in advance!! J

Where is the list stored?
If in a table, then:

select * from item_stores
where item in (select item from item_list)

If not stored anywhere, then
select * from item_stores
where item in ('item1','item2',...,'itemN')

 

[jason majchszak]

So i have my item list in a table which is a subset of my master list of items that contains 200,000 unique items which is in another table that has a seperate record for each item-store combination, so i have millions of rows of data in my master table. I want to make a query that returns the store(s) from my master table that have all of the items contained in my item list subset. So far I can only get it to return results on a single record basis, so it shows mw any store that contains at least 1 of the items in my subset. Any thoughts or ideas?

 

[Bob Barrows]

Show us what you've tried. I really don't have enought to go on here. Show
us a few rows of sample data from each table, the sql of the query you are
using to attempt to get what you want, the desired results you wish your
query to return, and the incorrect result that is actually being returned.

 

[John W. Vinson]

So i have my item list in a table which is a subset of my master list of items that contains 200,000 unique items which is in another table that has a seperate record for each item-store combination, so i have millions of rows of data in my master table. I want to make a query that returns the store(s) from my master table that have all of the items contained in my item list subset. So far I can only get it to return results on a single record basis, so it shows mw any store that contains at least 1 of the items in my subset. Any thoughts or ideas?

There's no really easy way to do this. My best suggestion would be to use a
subquery counting the number of distinct items at each store, and comparing
that count to the (known or counted using another query) the subset:

SELECT Table1.StoreInfo
FROM Table1
WHERE (SELECT Count(*) FROM
(SELECT ItemID FROM Table2
INNER JOIN SubsetTable ON Table2.ItemID=SubsetTable.ItemID
WHERE Table2.StoreID = Table1.StoreID))=
(SELECT Count(*) FROM SubsetTable);
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com