MS Access SQL Query Help

[joebloggs2004]

How do I select the most recent date in MS Access using the SQL statement:

SELECT requestDate
FROM STOCK_REQUEST
WHERE requestDate = ....??

[hspc]

Hi
Try this query:
SELECT requestDate
FROM STOCK_REQUEST
WHERE requestDate = (Select Max(requestDate) From STOCK_REQUEST)

[joebloggs2004]

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.

[hspc]

Hi
You 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) ..

[jim enright]

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]

pls ignore - put the code inside a try/catch block and ulimately
determined i transposed a "]" and a ")" - ie the syntax was bad - thanks