MS Access SQL Query Help
SELECT requestDate
FROM STOCK_REQUEST
WHERE requestDate = ...??
[155 byte] By [joebloggs2004] at [2007-11-19 1:50:34] 
How do I select the most recent date in MS Access using the SQL statement:
SELECT requestDate
FROM STOCK_REQUEST
WHERE requestDate = ...??
# 1 Re: MS Access SQL Query Help
HiTry this query:
SELECT requestDate
FROM STOCK_REQUEST
WHERE requestDate = (Select Max(requestDate) From STOCK_REQUEST)
hspc at 2007-11-9 13:38:45 >
# 2 Re: MS Access SQL Query Help
Duh, why didn't I think of that. I obviously just assumed MAX() could only be used for numbers. Thanks! :)Next problem I have is with this query:
SELECT PL.productNum, P.description, SUM(PL.quantity)
FROM PRODUCT AS P, PROD_LOCATION AS PL
WHERE P.productNum = PL.productNum
GROUP BY PL.productNum;
I keep getting this message: "You tried to execute a query that does not include the specified expression 'description' as part of an aggregate function." but I don't understand what it means. Can anyone explain what it means and how/where to fix the problem? I think the problem is with GROUP BY, but I'm unsure.
joebloggs2004 at 2007-11-9 13:39:56 >
# 3 Re: MS Access SQL Query Help
HiYou need to change the query like this :
SELECT PL.productNum, P.description, SUM(PL.quantity)
FROM PRODUCT AS P, PROD_LOCATION AS PL
WHERE P.productNum = PL.productNum
GROUP BY PL.productNum,P.description ;
When grouping .. You must specify all grouping levels in order of groups.
this means that you group by productNum then description (which is not useful in my opinion unless productNum is not unique) ..
hspc at 2007-11-9 13:40:54 >
# 4 Re: MS Access SQL Query Help
i am trying to do something analogous to this but it is not working.i am using vc++2005 and MSAccess 2003:
my code:
select
*
from
Objects
where
[ObjectType] in ('A','B')
and
[ObjectID] =
(select
max([ObjectID])
from
Objects
)
as far as my debugging can tell me the sql code is erroneous.
have also tried enclosing everything after the where in prentheses.
thanks for any help
jim enright at 2007-11-9 13:41:55 >
# 5 Re: MS Access SQL Query Help
pls ignore - put the code inside a try/catch block and ulimatelydetermined i transposed a "]" and a ")" - ie the syntax was bad - thanks
jim enright at 2007-11-9 13:42:51 >
