Help with selecting data

[Bob Quintal]

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];

 

[Etta]

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!

 

[Dennis]

You are evidently misunderstanding what "Forms Coding" means in the context
of this newsgroup. I suggest you repost your question in the QUERIES
newsgroup.

 

[Etta]

Thanks, Bob. I appreciate your help.

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];

 

[Etta]

Hi, Bob,

I received the error message "Cannot have aggregate functions in Group By
Clause," after adding the text printed below. What did I do wrong?

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

Etta

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];

 

[Etta]

Bob,

I received error message "Cannot have aggregate functions in Group By clause
([Form_Number] & " " & max([Ending_Number]))." What did I do wrong?

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]

In what context are you using the SQL? A query? A form recordsource? More
details would be useful.

I don't see the purpose of the concatenated data in the Select part; I would
just use this:

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

When you use an aggregate function - Max(), Count() etc - you don't put it in
the group by.

Your original post said something about forms ending in 0 - can you clarify
that for us?

John

Bob,

I received error message "Cannot have aggregate functions in Group By clause
([Form_Number] & " " & max([Ending_Number]))." What did I do wrong?
[quoted text clipped - 28 lines]

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

 

[Bob Quintal]

Hi, Bob,

I received the error message "Cannot have aggregate functions in
Group By Clause," after adding the text printed below. What did I
do wrong?

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

Etta

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

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];

 

[Etta]

Thanks all for responding to my question.

What I am attempting to do (badly!) is automate the creation of Cover Sheets
to use when we issue packages of forms. Currently, we manually type the
form’s title and number in a Word document and then merge it with a data
source to create Cover Sheets containing the appropriate number of forms
issued (for example, 0001, 0002, 0003, 0004, 0005). Since the form’s title,
number and last number issued are stored in our DocumentsIssued Table, it
makes sense to use Access to create the Cover Sheets.

To accomplish my goals someone suggested that I create a subform that lists
the forms issued and maximum (last) number used for each and then use the
subform to open Word and the Cover Sheet. (I have with Arvin Meyer’s “Sub
cmdMergeLetter_Click†code (© 1999 to do the latter.)

Here’s where I am now: my subform shows all of the forms issued and the
ending number for each package, however, it also include forms that have not
yet been issued (the 0s I mentioned previously). For example:
XX-YY-01 / 25
XX-YY-01 / 50
XX-YY-01 / 75
XX-YY-02 / 0
XX-YY-03 / 0
XX-YY-04 / 15

Once I get the subform working then I will attempt to use Arvin’s code.

Does this plan sound reasonable and is it the most effective approach?