Results 1 to 4 of 4
  1. #1
    clindner.iv is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2016
    Posts
    8

    Query to pick the Best Match in Client Preferances

    Hi



    I'm writing a query to determine the best match for a recipe based on what the client says they like most

    There are two Variable i need to match

    1. the meal type for the day (there are 4 choices)
    2. the clinets score for each meal type (score 1-9)

    Example:
    Client 1 gave the following scores for each meal type

    Choice 1 = 5
    Choice 2 = 4
    Choice 3 = 9
    Choice 4 = 1

    I need the query to return the value of Choice 3 and insert into their order table field choice 3
    the query has to match the recipes with the choices and show the scores. then insert into the order table that order for that date.

    This is the SQL Statement but using the Max formula give me an error
    INSERT INTO Tbl_Orders ( Fld_ClientID, Fld_OrderDate, Fld_MealType )
    SELECT Tbl_Client_Prefrences.Fld_ClientID, TBl_Calendar.Fld_meal_Date, TBl_Calendar.Fld_MealType
    FROM Tbl_Client_Prefrences LEFT JOIN TBl_Calendar ON Tbl_Client_Prefrences.Fld_Ingdient = TBl_Calendar.Fld_RecID
    WHERE (((Tbl_Client_Prefrences.Fld_ClientID)=1) AND ((TBl_Calendar.Fld_meal_Date)=#12/1/2016#) AND ((Tbl_Client_Prefrences.Fld_Score)=Max([fld_score])));

    Any help would be great.

    Thanks

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Interesting. A sample db we can play with may help. Welcome to the site by the way!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    clindner.iv is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2016
    Posts
    8
    I can share it but there wont be any data to look at if that is OK. All the data i have is Private So i would have to copy and delete any client information.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Hard to test with no data. How about fake but representative data?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 1
    Last Post: 07-31-2014, 10:13 PM
  2. Replies: 5
    Last Post: 09-25-2013, 02:20 PM
  3. Replies: 1
    Last Post: 07-26-2011, 06:10 AM
  4. Query to match job and client skills?
    By kbp in forum Access
    Replies: 5
    Last Post: 01-27-2011, 05:15 AM
  5. How to pick the n-th row from a query
    By Johnny C in forum Queries
    Replies: 2
    Last Post: 07-27-2010, 05:14 AM

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