MAX of Text Field Date, Inner Join

[natalie99]

Hello :)

I am having trouble returning a single most recent transaction from my db files, could someone please tell me how to do the following...

I have two tables

tblBATCH
tblINVENTORY

both tables have a field named BATCH

BATCH in the inventory is in this format

NOV07
MAR06
JAN08

BATCH in tblBATCH assigns a value to each month & year combination like so..

BATCH.......... ............... ..............V ALUE

JAN07.......... ............... ............... ....1
FEB07.......... ............... ............... ...2
MAR07.......... ............... ............... ..3

tbl INVENTORY contains multiple records with the same ID number, I want to run a query to only return one line of data per ID number, which is the most recent line entry, ie. highest value in tblBATCH.[Value]

How do I do this? I have tried the following with no success:

SELECT tblINVENTORY.[ID], tblINVENTORY.BA TCH, Max([value]) AS Value_max
FROM tblINVENTORY INNER JOIN tblBATCH ON tblINVENTORY.BA TCH = tblBATCH.BATCH;

It tells me that ID is not part of the aggregate function? Though this is the information I am trying to return??

What am i doing wrong, PLEASE PLEASE help me!! :D

Nat

[natalie99]

Hi everyone

I have this code working:

SELECT qryBATCH_val.[ID], Max(qryBATCH_va l.Value) AS MaxOfValue
FROM qryBATCH_val
GROUP BY qryBATCH_val.[ID];

however, I can't add in other fields from qryBATCH_val..

help? please?

how do I add in other fields without giving them a Group By??

:)

nat