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