Here's a tough one

Results 1 to 2 of 2

Thread: Here's a tough one

  1. #1
    Paully Guest

    Default Here's a tough one

    I have an online store. I am trying to make product recommendations to customers based upon what the other customers that purchased that item also purchased.<BR><BR>I have an Access 97 database with an order_details table that contains one record for each item purchased along with a corresponding order number. I need to have an efficient way of preforming the following pseudo-code<BR><BR>Select all ordernumbers and where the record contains thisproduct<BR><BR>For each ordernumber <BR>select productcode <BR><BR>Somehow evaluate the top three purchased products<BR><BR>And display the results<BR><BR>------Actual code----<BR><BR>&#039;Everthing ok to here<BR><BR>SQL1 = "SELECT OrderID FROM Order_Details WHERE (ProductCode = &#039;"<BR>SQL1 = SQL1 & RstObj2("ProductCode") <BR>SQL1 = SQL1 & "&#039;)"<BR><BR>Set rs_Orders = Server.CreateObject("ADODB.Recordset")<BR><BR>rs_O rders.Open SQL1, ConnObj2, adOpenKeyset<BR><BR>If not rs_Orders.EOF then<BR><BR>OrderIDArray = rs_Orders.GetRows<BR><BR>Set rs_Orders = Nothing<BR><BR>&#039;Delete from all records from the temporary storage table<BR><BR>SQL3 = "Delete * From Recomend"<BR>ConnObj2.Execute(SQL3)<BR><BR><BR>For i = 0 to UBOUND(OrderIDArray, 2)<BR> SQL = "SELECT DISTINCT ProductCode FROM Order_Details WHERE OrderID = &#039;" & OrderIDArray(0 , i) & "&#039; ORDER BY OrderID"<BR> Set rs_ProductList = Server.CreateObject("ADODB.Recordset")<BR> rs_ProductList.Open SQL, ConnObj2, adOpenKeyset<BR> ProductCodeArray = rs_ProductList.GetRows<BR> Set rs_ProductList = Nothing<BR> Response.Write(Timer - Time1)<BR> For z = 0 to UBOUND(ProductCodeArray, 2)<BR> If ProductCodeArray(0 , z) &#060;&#062; RstObj2("ProductCode") then<BR> SQL4 = "INSERT INTO Recomend(ProductCode) VALUES (&#039;" & ProductCodeArray(0 , z) & "&#039;)"<BR> ConnObj2.Execute(SQL4)<BR> end if<BR> Next<BR>Next <BR><BR>&#039;TotalDups is a query of the temporary storage table that groups and count the total occurances<BR><BR>SQL6 = "SELECT * FROM TotalDups WHERE NumberOfDups &#062; 0 ORDER BY NumberOfDups DESC"<BR>Set rs_3 = Server.CreateObject("ADODB.Recordset")<BR>rs_3.Ope n SQL6, ConnObj2, adOpenKeyset<BR>Else<BR>Orders_Ever = 1<BR>Set rs_Orders = Nothing<BR>End if<BR><BR>&#039; every thing fine after here

  2. #2
    Join Date
    Dec 1969

    Default How about all in one SQL statement?

    SELECT TOP 3 Count(ProductCode), ProductCode <BR>FROM OrderDetails<BR>GROUP BY ProductCode<BR>ORDER BY Count(ProductCode) DESC<BR><BR>That does it. Kablooey. Tested in Access, but should work in any ANSI SEL compliant system.<BR><BR><BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts