Counting Unique Combinations of Item #s and Store #s

[Ryan86]

I have a table that has the two columns... Item # and Store #. Each store can have any combination of hundreds of items, so store numbers are repeated in column A for each new item # listed in that store in column B. I need to find a way to count how many stores have the exact same combination of items in there stores. So if Store 1 and Store 2 both stock the exact same combination of items it would great to somehow assign a unique ID to that combination and count how many there are. I have tried using crosstab queries to sort the store #s into rows and then item number across the headings, but I still then have the same problem of needing to know how many entire records are unique. Perhaps it is easier from this point see attached small mockup table for an example of what the crosstab query would generate (Coluumn headers are item #s). The Y signifies that item is stocked in the that store. From that point I would need to write a query to determine how many unique entire records there are and then count them.

Store #	20	21	22	23	34
1	Y	Y	Y
2	Y	Y	Y
3		Y	Y
4		Y			Y
5		Y			Y

[orange]

Show us the design of this table

I have a table that has the two columns... Item # and Store #

[Ryan86]

Attached are the Table view and Design view. As you will see there will be more field names, than just item and store but those are irrelevant as to what I am trying to do, essentially I can delete all that data.

[orange]

Try this query (untested) and see if it's what you're looking for.

Select StoreNumber,StoreNumber & ItemNumber, Count(ItemNumber)
FROM YourTableName
Group By
StoreNumber,StoreNumber & ItemNumber;

[Ryan86]

Try this query (untested) and see if it's what you're looking for.

This doesn't achieve what I am trying to accomplish. Here maybe is a better understanding of what I am trying to get. In the attached picture I have run a crosstab query that shows store numbers in the Rows, and item numbers across the columns. Each row shows (via a 1 indicator) which items are stocked in that store. What I then need to accomplish is know how many stores share the exact same combination of items. In the provided example it would be 3 different combinations, highlighted different colors. Its almost like I need to find out how many rows are entirely duplicated (exluding the store number).

[orange]

I think you should repost and include Help with CrossTab query in your write up.