Results 1 to 3 of 3
  1. #1
    dtl is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Posts
    1

    Users who bought this product also bought these related products

    Hello,
    I have a large dataset of customers who have bought a product from me. What I want to be able to do is map in Access a list of other products that were purchased by customers who also bought the same product as you.

    Example, Peter bought an orange, an apple and a banana. Simon comes to the site and buys a banana, so I want to be able to say, other customers who also bought a banana bought an apple and an orange.

    Rather than listing every single other product bought though, I want to rank. So of the 200 customers who also bought a banana, 50 of them went on to buy an apple whereas only 5 of them went on to buy an orange. Therefore an apple is a better suggested next purchase.

    Any ideas on how to create this within Access?



    Thanks for any help

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Make Q1 that has Simons banana.
    Q2 uses Q1 and joins to the data table on ITEM to pull all other people orderIDs having banana.
    Q3 uses Q2 orderIDs to pull all their items and counts them.

  3. #3
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Q1 -remove duplicate orders:
    SELECT DISTINCT Table1.Customer, Table1.Product FROM Table1;

    Q2 -who bought what other product :
    SELECT DISTINCT q1.Customer, q1.Product, Table1.Customer, Table1.Product
    FROM q1 INNER JOIN Table1 ON q1.Customer = Table1.Customer
    WHERE (((Table1.Product)<>[q1].[Product]));


    Q3 - count:
    SELECT q2.q1.Product, q2.Table1.Product, Count(q2.Table1.Customer) AS CountOfTable1_Customer
    FROM q2
    GROUP BY q2.q1.Product, q2.Table1.Product;


    Maybe!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 02-28-2015, 11:57 AM
  2. Replies: 18
    Last Post: 11-12-2013, 04:06 PM
  3. Replies: 3
    Last Post: 10-28-2013, 04:39 PM
  4. Replies: 2
    Last Post: 03-07-2013, 02:25 PM
  5. Replies: 8
    Last Post: 04-24-2012, 01:05 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums