Results 1 to 6 of 6
  1. #1
    VBNoober is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2016
    Posts
    2

    California Wine Enthusiast - Simple Query Design Problem

    I have 4 tables:
    Products
    Customers
    Orders
    Order Details

    I want to query all products that the selected customer has not yet ordered.
    This would be used as a suggestion box for our sales reps.

    I have something to the fact of
    ProductName | Vintage | Category | Alcohol Percent | CustomerID **Not Shown* | Full Name | ShipDate

    CustomerID Criteria is from combobox and can be referenced [Forms]![Add or Delete Order]![CustomerID]

    I'm sure this can be done with Query Design but I'm also willing to use VisualBasic.
    I'm familiar with php/SQL so I've been dabbling in VisualBasic and have also come up with this (not working):



    Code:
    Dim strsearch As String
    Dim strHistoryp As String
    Dim strHistoryc As String
    Dim strHistoryo As String
    
    
    strHistoryc = Me.CurrentCustomer.Value
    strHistoryo = "SELECT OrderID From Orders WHERE (([CustomerID] = 'strHistoryc' ))"
    strHistoryp = "SELECT ProductID From Order Details WHERE (([OrderID] = 'strHistoryo' ))"
    strsearch = "SELECT * FROM Products WHERE (([ProductID] <> 'strHistoryp' or [ProductID] is null))"
    
    
    Me.RecordSource = strsearch
    I'm very new. Please advise best practices.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you post a copy of your evolving database so we can get some records for test?
    Best to zip , then attach the zip file.

  3. #3
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It can be done with 2 queries. The first selects all the products that customer has ordered. Then use the unmatched query wizard to compare the product table to that query, which should return all the products they haven't ordered.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    VBNoober is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2016
    Posts
    2
    Thank you pbaldy! I knew it was very simple!
    Last edited by VBNoober; 05-21-2016 at 02:55 PM. Reason: New problem

  5. #5
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help and welcome to the site by the way!

    I'll probably enjoy a little wine tonight.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Further to Paul's suggestion re 2 queries, I mocked up 4 tables (minimal data)

    First query to show which Products "Bob" has ordered
    Code:
    SELECT WineCustomer.CustId, WineCustomer.CustName, WineCustOrders.Orderid, WineProducts.pRODnAME
    FROM WineProducts INNER JOIN ((WineCustomer INNER JOIN WineCustOrders ON WineCustomer.CustId = WineCustOrders.CustId) INNER JOIN WINEORDERDETAILS ON WineCustOrders.Orderid = WINEORDERDETAILS.Orderid) ON WineProducts.pRODID = WINEORDERDETAILS.PRODId
    WHERE (((WineCustomer.CustName)="Bob"))
    ORDER BY WineCustomer.CustName, WineCustOrders.Orderid;
    Then to find the Products that Bob hasn't yet ordered,
    as Paul suggested, take all the Products and remove the one's that Bob has already ordered.

    Code:
    SELECT WineProducts.pRODnAME as ProductsNotYetOrderedByBOB
    FROM WineProducts 
    WHERE 
    WineProducts.ProdName Not In
     (select prodName from
      (
    SELECT WineCustomer.CustId, WineCustomer.CustName, WineCustOrders.Orderid, WineProducts.pRODnAME
    FROM WineProducts INNER JOIN ((WineCustomer INNER JOIN WineCustOrders ON WineCustomer.CustId = WineCustOrders.CustId) INNER JOIN WINEORDERDETAILS ON WineCustOrders.Orderid = WINEORDERDETAILS.Orderid) ON WineProducts.pRODID = WINEORDERDETAILS.PRODId
    WHERE (WineCustomer.CustName)="Bob"
    )
      )

    I didn't add all of the fields you mentioned, but I hope there is sufficient info to show the approach.

    You can use this as a guide and substitute your own fields and tables.
    Good luck.
    Attached Thumbnails Attached Thumbnails Wine.jpg   WineTables.jpg  
    Last edited by orange; 05-21-2016 at 02:15 PM. Reason: updated query

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

Similar Threads

  1. Replies: 2
    Last Post: 11-19-2012, 10:54 AM
  2. Query Design Problem
    By hardleydirt in forum Queries
    Replies: 5
    Last Post: 11-16-2012, 01:58 PM
  3. Problem creating a simple query
    By perksy in forum Access
    Replies: 2
    Last Post: 01-03-2012, 09:42 AM
  4. Replies: 3
    Last Post: 07-18-2011, 08:03 AM
  5. Simple query problem
    By rajnag in forum Access
    Replies: 4
    Last Post: 08-19-2010, 05:09 AM

Tags for this Thread

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