Help with selecting data

[Etta]

I posted and received a response to my question (see below) about getting my
database to come up with a list of forms issued and the last number issued
for each. Bob Quintal was kind enough to respond and I tried his suggestion
but was unsuccessful. After cutting, pasting and saving his epxression, I
received an error message stating "Cannot have aggregate function in Group By
clause..." I believe I cut and pasted his text accurately,and I can't figure
out what I did wrong.

Any suggestions on how I can get the database to do what I need it to do?

By: Bob Quintal In: microsoft.public.access.formscoding

Can someone correct the following expression so that my database
comes up with a list of forms issued and the highest number issued
for each?

SELECT [ID], [Form_Number] & " " & [Ending_Number] FROM
DocumentsIssued ORDER BY [Form_Number] & ", " & [Ending_Number];

Currently, the above is generating all the forms and all the
ending numbers. It also includes forms that have not been issued
with the Ending Number of 0.

To be clear, here's a sample of what I get, followed by what I
need:

Form A 15
Form A 30
Form A 90
Form B 10
Form C 30
Form C 60

What I need is:
Form A 90
Form B 10
Form C 60

Thanks in advance for your help!

SELECT [ID], [Form_Number] & " " & max([Ending_Number]) FROM
DocumentsIssued
GROUP BY [ID], [Form_Number] & " " & max([Ending_Number])
ORDER BY [ID],[Form_Number];

 

[J_Goddard via AccessMonster.com]

Hi -

I posted a response in the other NG. The cause of the error message you are
getting is the Max(Ending_Number) in the group by clause. Max() is one of
the aggregate functions.

John

I posted and received a response to my question (see below) about getting my
database to come up with a list of forms issued and the last number issued
for each. Bob Quintal was kind enough to respond and I tried his suggestion
but was unsuccessful. After cutting, pasting and saving his epxression, I
received an error message stating "Cannot have aggregate function in Group By
clause..." I believe I cut and pasted his text accurately,and I can't figure
out what I did wrong.

Any suggestions on how I can get the database to do what I need it to do?

By: Bob Quintal In: microsoft.public.access.formscoding

Can someone correct the following expression so that my database
comes up with a list of forms issued and the highest number issued

[quoted text clipped - 23 lines]

Thanks in advance for your help!

SELECT [ID], [Form_Number] & " " & max([Ending_Number]) FROM
DocumentsIssued
GROUP BY [ID], [Form_Number] & " " & max([Ending_Number])
ORDER BY [ID],[Form_Number];