the one before maxDate!

[installer69]

I have a query that finds the 'max' value of a now() field but how can I find the one before the max?

 

[M8KWR]

instead of using now() use date() -1.

That will give you todays date, minus 1 day.

I hope that is want you wanted if not let me know.

 

[installer69]

Sorry, I didn't give all the info.

The field that stores the now() data is a datestamp field for movements of stock. i use the maxDate lookup to find the last movement of each stock item. What I ned to do now is find the last but one movement of each item.

Craig

 

[PHV]

Something like this ?
SELECT A.itemfield,Max(A.nowfield) As maxBut1Date FROM theTable A INNER JOIN
(SELECT itemfield,Max(nowfield) As maxDate FROM theTable GROUP BY itemfield) B
ON A.itemfield = B.itemfield
WHERE A.nowfield < B.maxDate
GROUP BY A.itemfield
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[installer69]

It works fine until I actually try to use it in the application. then I get an error about somethin like > and < are not supported and the query changes to:

SELECT A.serial, Max(A.actionDate) AS maxBut1Date
FROM master AS A INNER JOIN [SELECT serial,Max(actionDate) As maxDate FROM master GROUP BY serial] AS B ON A.serial=B.serial
WHERE A.actionDate<B.maxDate
GROUP BY A.serial;

Notice the 'AS' before A INNER JOIN and the () changed to [] around the second SELECT query

 

[cmmrfrds]

Access seems to change the parens () denoting a derived query into brakets[]. with a period - not sure if this is always the case. Try putting the period after the bracket.

SELECT A.serial, Max(A.actionDate) AS maxBut1Date
FROM master AS A INNER JOIN [SELECT serial,Max(actionDate) As maxDate FROM master GROUP BY serial]. AS B ON A.serial=B.serial
WHERE A.actionDate<B.maxDate
GROUP BY A.serial;

 

[installer69]

Thanks for the reply but I ended up curing the problem by inserting a space between the , and the Max. Compare:

SELECT A.itemfield, Max(A.nowfield) As maxBut1Date FROM theTable A INNER JOIN
(SELECT itemfield, Max(nowfield) As maxDate FROM theTable GROUP BY itemfield) B
ON A.itemfield = B.itemfield
WHERE A.nowfield < B.maxDate
GROUP BY A.itemfield
;

and

SELECT A.itemfield,Max(A.nowfield) As maxBut1Date FROM theTable A INNER JOIN
(SELECT itemfield,Max(nowfield) As maxDate FROM theTable GROUP BY itemfield) B
ON A.itemfield = B.itemfield
WHERE A.nowfield < B.maxDate
GROUP BY A.itemfield
;

Hope this helps someone else.