Max Date

[brucey54]

Hi folks, need some help with displaying the max date sql code below;

SELECT TblDietPlan.DietPlanID, TblDietPlan.ClientID, TblDietPlan.MealDate, TblDietPlan.MorningSnack, TblDietPlan.AfternoonSnack, TblDietPlan.EveningSnack
FROM TblDietPlan
WHERE (((TblDietPlan.ClientID) Is Not Null) AND ((TblDietPlan.MealDate)=Max()))
ORDER BY TblDietPlan.MealDate DESC;

 

[CJ_London]

better explain Max of what? last meal for the client? last meal for everyone, last time the client had that meal, last time any client had that meal?, etc

 

[brucey54]

lol sorry, TblDietPlan.MealDate is a date field, trying to display a list of clientsID's and what was their last meal.

i.e. Client 1234
MealDate 13/04/2015
TblDietPlan.MorningSnack - Bread & water
TblDietPlan.AfternoonSnack - Bread & water
TblDietPlan.EveningSnack - T-bone steak & Peroni beer

ps this query will feed a form, the query will need to allow the data on the form to be edit on!

 

[CJ_London]

there are three solutions - from the fastest/most complex to the slowest/simplest

SELECT TblDietPlan.DietPlanID, TblDietPlan.ClientID, TblDietPlan.MealDate, TblDietPlan.MorningSnack, TblDietPlan.AfternoonSnack, TblDietPlan.EveningSnack
FROM TblDietPlan INNER JOIN (SELECT ClientID, Max(MealDate) AS LastDate FROM tblDietPlan GROUP BY ClientID) AS Latest ON TblDietPlan.ClientID=Latest.ClientID
ORDER BY TblDietPlan.MealDate DESC;

[COLOR=#000000][COLOR=#000000][COLOR=#000000][COLOR=#0000bb][COLOR=#0000bb][COLOR=#000000][COLOR=black][COLOR=#0000bb]SELECT TblDietPlan[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000bb]DietPlanID[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000bb]TblDietPlan[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000bb]ClientID[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000bb]TblDietPlan[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000bb]MealDate[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000bb]TblDietPlan[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000bb]MorningSnack[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000bb]TblDietPlan[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000bb]AfternoonSnack[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000bb]TblDietPlan[/COLOR][COLOR=#007700].[/COLOR][/COLOR][COLOR=black][COLOR=#0000bb]EveningSnack[/COLOR]
[COLOR=black]FROM TblDietPlan [/COLOR]
[COLOR=black]WHERE [/COLOR][COLOR=#0000bb]TblDietPlan[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000bb]MealDate[/COLOR][COLOR=#007700]=(SELECT Max(MealDate) AS LastDate FROM tblDietPlan AS L WHERE ClientID=tblDietPlan.ClientID)[/COLOR][/COLOR]
[COLOR=black][COLOR=#0000bb]ORDER BY TblDietPlan[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000bb]MealDate DESC[/COLOR][COLOR=#007700];[/COLOR][/COLOR][/COLOR][/COLOR]
[/COLOR][/COLOR][/COLOR][/COLOR]

[COLOR=#000000][COLOR=#000000][COLOR=#000000][COLOR=#0000bb][COLOR=#0000bb][COLOR=#0000bb][COLOR=#000000][COLOR=black][COLOR=#0000bb]SELECT TblDietPlan[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000bb]DietPlanID[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000bb]TblDietPlan[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000bb]ClientID[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000bb]TblDietPlan[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000bb]MealDate[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000bb]TblDietPlan[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000bb]MorningSnack[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000bb]TblDietPlan[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000bb]AfternoonSnack[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000bb]TblDietPlan[/COLOR][COLOR=#007700].[/COLOR][/COLOR][COLOR=black][COLOR=#0000bb]EveningSnack[/COLOR]
[COLOR=black]FROM TblDietPlan [/COLOR]
[COLOR=black]WHERE [/COLOR][COLOR=#0000bb]TblDietPlan[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000bb]MealDate[/COLOR][COLOR=#007700]=DMax("[MealDate]","[tblDietPlan]","ClientID=" & ClientID)[/COLOR][/COLOR]
[COLOR=black][COLOR=#0000bb]ORDER BY TblDietPlan[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000bb]MealDate DESC[/COLOR][COLOR=#007700];[/COLOR][/COLOR][/COLOR][/COLOR]
[/COLOR][/COLOR][/COLOR][/COLOR][/COLOR]

I've take out the 'is not null' criteria as it shouldn't be required (certainly not for the first one)

 

[brucey54]

Thanks CJ_London, your help was much appreciated