Sean said:
I need to know if there is a way to do this in a query and if someone
could
supply the SQL for it. I have a table with 2 fields, OrderNumber and
ItemNumber. There is a years worth of data. The order number will be
unique
but the items will appear across many different orders. What I want to
be
able to do is determine if ITEM1 sold on X amount of orders, these other
items sold with it Z percentage of the time. So mabye the query
determines
the item that sells the most and the other items appear on the same order
X
percent of the time. Then the next line of data is the 2nd fastest
selling
item appeared on x amount of orders and the following items sold with it
x
percent of the time and so on. I want to avoid having to do this one
item
at
a time by entering each one becuas there is way too much data.
Can this be done with 1 query, or multiple query's? Could it be done
with
code?
I could be wrong but I imagine Access would not
support the amount of subquery nesting you would
need.
I imagine setting up a table w/ 4 fields
tblCommonItems
ItemNumber Text
OrderCnt Long
CommonItem Text
CommonCnt Long
then following code for a command button:
'**** start code *****
Private Sub cmdGetCommonItems_Click()
On Error GoTo Err_cmdGetCommonItems_Click
Dim strTblName As String
Dim strItemNumber As String 'maybe long?
Dim db As DAO.Database
Dim rs As DAO.Recordset
strTblName = "qryOrderItem"
DoCmd.Hourglass True
Set db = CurrentDb
'clear table
db.Execute "DELETE * FROM tblCommonItems", dbFailOnError
'open a recordset to distinct items.
strSQL = "SELECT DISTINCT ItemNumber FROM " & strTblName _
& " ORDER BY ItemNumber;"
Set rs = db.OpenRecordset(strSQL)
rs.MoveFirst
Do While Not rs.EOF = True
strItemNumber = rs!ItemNumber
strSQL = "INSERT INTO tblCommonItems " _
& "(ItemNumber, CommonItem, CommonCnt) " _
& "SELECT '" & strItemNumber & "', " _
& "M.ItemNumber, COUNT(*) FROM " _
& strTblName & " As M WHERE " _
& "M.ItemNumber <> '" & strItemNumber & "' AND " _
& "M.OrderNumber IN (SELECT q.OrderNumber " _
& "FROM " & strTblName & " As q WHERE " _
& "q.ItemNumber = '" & strItemNumber & "') " _
& "GROUP BY M.ItemNumber;"
'Debug.Print strSQL
db.Execute strSQL, dbFailOnError
strSQL = "UPDATE tblCommonItems " _
& "SET OrderCnt = DCount('ItemNumber'," _
& "'" & strTblName & "','[ItemNumber]=''" _
& strItemNumber & "''') WHERE ItemNumber ='" _
& strItemNumber & "';"
'Debug.Print strSQL
db.Execute strSQL, dbFailOnError
rs.MoveNext
Loop
rs.Close
db.Close
MsgBox "Successfully filled tblCommonItems."
Exit_cmdGetCommonItems_Click:
If Not rs Is Nothing Then Set rs = Nothing
If Not db Is Nothing Then Set db = Nothing
DoCmd.Hourglass False
Exit Sub
Err_cmdGetCommonItems_Click:
MsgBox Err.Description
Resume Exit_cmdGetCommonItems_Click
End Sub
'**** end code ****
I made a simple table
tblSean
OrderNumber ItemNumber
1 Itm1
1 Itm2
1 Itm4
2 Itm1
2 Itm3
3 Itm2
3 Itm3
3 Itm4
4 Itm2
4 Itm3
4 Itm4
5 Itm1
5 Itm2
6 Itm3
6 Itm4
then defined a query
qryOrderItem
SELECT tblSean.OrderNumber, tblSean.ItemNumber
FROM tblSean;
make above query using your table
(maybe even filtering for order dates...)
I then ran the sub above and got:
tblCommonItems
ItemNumber OrderCnt CommonItem CommonCnt
Itm1 3 Itm2 2
Itm1 3 Itm3 1
Itm1 3 Itm4 1
Itm2 4 Itm1 2
Itm2 4 Itm3 2
Itm2 4 Itm4 3
Itm3 4 Itm1 1
Itm3 4 Itm2 2
Itm3 4 Itm4 3
Itm4 4 Itm1 1
Itm4 4 Itm2 3
Itm4 4 Itm3 3
Let's see if thinking right....
OrderCnt
CommonCnt
Itm1 was in "orders" 1 2 5 3
Itm2 in same? x x 2
Itm3 in same? x 1
Itm4 in same? x 1
Itm2 was in "orders" 1 3 4 5 4
Itm1 in same? x x 2
Itm3 in same? x x 2
Itm4 in same? x x x 3
It *looks* like it was working right....