Top 100 combinations

[SirPoonga]

I am trying to find some sales statistics.

I have part numbers which consist of componants.
Per each order, a part may have different componant parts.
Such as for one order you may see these relationships (quantity in
parens)

order id 1
123456-7890
4332 (5)
5443 (2)
5253 (3)

order id 2
123456-7890
4332 (2)
5443 (3)
5253 (2)

order id 3
123456-7890
4332 (2)
5443 (2)
5243 (5)
2457 (5)

order id 4
523624-1636
8926 (2)
5683 (2)
4567 (5)
4553 (5)

So you get these combinations being sold. I need to find the top 100
combinations sold.

The table that had the part to componant relationship will contain an
ID_ORD, ID_ITEM, ID_ITEM_COMP, QTY for the order id, item number,
componant number, and quantity of the componant.

 

[SirPoonga]

Update, I forgot that the top level item has a quantiy too which is
stored in another table. So it's a combination of the item IDS with
componants and quantities for the item and componant.

 

[SirPoonga]

Update, I forgot that the top level item has a quantiy too which is
calculated from another table. So it's a combination of the item IDS
with componants and quantities for the item and componant.

 

[SirPoonga]

There's gotta be a way to do this in a set of queries. However I think
this logic should work.

For the parts I need to do this for there should only be one top level
item number per order.

I'm thinking in VBA I will do something like this
Query all the OrderID/ITEM combinations.
Loop through that recordset
for each record run the query to calculate the quantity of the parent
item sold.
Create a string "parentID parentQTY "
Then query all the componant items and quantitys for the current
orderID/ITEM combination in assending order
for each of those records append the componant Id and quantity to the
string.
Dump that string to a table that allows duplicates.
Then to a count on that table and take top 100.

Logically that should work. Though I;d really wnt to know if there was
a qay to do it completely in a query.