Items With Which items Most Commonly

[vansicklej]

Folks,

I have a massive database with 123 columns, but I think I should be able to do what I'm looking for by utilizing only 2 of the columns:

Item#
Invoice#

What I'd like to do, is compare the item#'s on each invoiceto see which ones most commonly ship together.

On table CurrentInv
Item# Invoice#
aaa 34621
bbb 34621
ccc 34621


aaa 34622
bbb 34622
ddd 34622
aaa 34623
bbb 34623
eee 34623

So we see that Item#'s aaa and bbb most commonly ship together on the same invoice. Of course I'm looking at a much larger database than this, but in it's simplified form, here's what I'd like to do.
The results would be:
aaa
bbb
And if possible how many times they've shipped together on the same invoice.

In addition, there are certain Item#'s that I don't care what they've shipped with at all, so I'd like to omit those, call them YYY.

Thanks for any help!

[NoellaG]

Hi,

included you'll find an example that looks for the number of times an item occurred with another using a groups query. I assumed the database has the standard invoice -> invoice lines design where 1 invoice can have many lines.

grNG

[vansicklej]

Thanks for the reply Noella!

One issue with this however is that we have hundreds of thousands of item#'s, so changing the dropping down for every item to see what it ships with probably isn't the most efficient way to view this data.

What would work better is if we could display these items in a table in order of count that it shipped with another item, and what that item is. So the top of the table would show the items that most commonly ship together, and decreases as you go down.

Thanks!

[rpeare]

Someone with more talent than me may be able to do this with queries, but I've written an example (attached) to show how to do it with vb code. Note this assumes that you have a table for your invoices and a table for all of your items on that invoice and that your database is normalized