Grouping Frequency

[Guest]

I'm trying to determine the number of times items are found together on a
delivery in a database. Each delivery is a unique number with multiple
items. Each line of the data base is unique to an item/delivery combination.
So, if the delivery has 10 items, there will be 10 lines, one fore each item
with the delivery number repeated 10 times. The number of items is large
(1500-1700) so a crosstab with items across and items down with the count of
deliveries in the middle won't work. The number of items on a delivery
ranges from 1 to 100. Any other ideas on how to accomplish this? Here's a
simplified example of the data:

Item Quantity Delivery
brown 4 0001
blue 10 0001
green 3 0001
brown 5 0002
yellow 2 0002
green 20 0003
blue 10 0004
green 5 0004

 

[Dale Fye]

It is a little difficult to determine exactly what you are looking for.

Could you describe what the output you desire would look like, given the
example below as your input table?

Do you just want the 2 item combinations, or do you want all of the various
combinations that are possible for each delivery? For example: Delivery #1
has X ways that the three items can be grouped (as groups of two or more,
you might even want to consider "groups" of 1).

Brown, Blue, Green
Brown, Blue
Brown, Green
Blue, Green

The last combination is also available in Delivery #4.

With a delivery of 10 items, there are 45 combinations of items taken 2 at a
time,
120 combinations of
items taken 3 at a time,
210
4
...

Overall, there are 1022 different ways you can combine 1 to 10 items from a
ten item delivery. Please advise how you would like to group these.

 

[Guest]

Dale,
I'm looking for which items ship together, so just the number of times each
item ships with another (and which never ship together). I don't care about
the quantity, only the pairing. So it's the frequency of paring between any
two items. The output would be something like this:

Brown Blue 1
Brown Green 1
Brown Yellow 1
Blue Green 2
Blue Yellow 0
Green Yellow 0

 

[Guest]

Any suggestions???

Dale,
I'm looking for which items ship together, so just the number of times each
item ships with another (and which never ship together). I don't care about
the quantity, only the pairing. So it's the frequency of paring between any
two items. The output would be something like this:

Brown Blue 1
Brown Green 1
Brown Yellow 1
Blue Green 2
Blue Yellow 0
Green Yellow 0

 

[Dale Fye]

Well, if you have 1500 items in your list of items, then there are 1500 *
1499/2 or (1,124,250) different combinations of two items that could be
delivered in the same order, so the first thing you need to do is get a
query which will have two columns and the 1124250 rows. This query would
look something like(save this as qry_Item_Combinations):

SELECT T1.ItemID as FirstItem, T2.ItemID as SecondItem
FROM yourItemTable as T1, yourItemTable as T2
WHERE T2.ItemID > T1.ItemID

Then you will need another query that links this query to two copies of your
Delivery Table.

SELECT Q1.First_Item, Q1.Second_Item,
Sum(IIf([D1].[DeliveryID]=[D2].[DeliveryID],1,0)) AS Expr1
FROM tbl_Deliveries AS D2
INNER JOIN (tbl_Deliveries AS D1 INNER JOIN qry_Item_Combinations AS Q1
ON D1.ItemID = Q1.First_Item)
ON D2.ItemID = Q1.Second_Item
GROUP BY Q1.First_Item, Q1.Second_Item;

What this query does is count (Sum) all of the times that each of the
combinations of items shows up in your deliveries. Try this with a small
table to get an understanding of how it works, and why.

Good Luck.

Dale