Selecting Earliest and latest date

[kstyles]

I have a table called notes. It tracks the note date and the note corresponding to a specific annual review. IE - there is only one annual review number (in another table as the key) and many notes. I want to make a query to select the annual review number and the last note date and the earliest note date so I can make a report. I have tried using the SQL to input Max for the note date... so far I keep getting error messages. If I can make two seperate queries- one for the latest note date and one for the earliest that may be better and fit into my report scheme.

Any help would be greatly appreciated!

[pbaldy]

Try

SELECT ReviewNumber, Max(NoteDate) AS LastDate, Min(NoteDate) AS FirstDate
FROM TableName
GROUP BY ReviewNumber

You'll obviously have to input the correct names. If you want other info, this might help:

http://www.baldyweb.com/LastValue.htm

[ssanfu]

If you have a form with a button to open the report, add two text boxes.
Name one dteFrom and the other dteTo.

The query for the report would look like:

SELECT ReviewNumber, NoteDate, Notes
FROM TableName
WHERE [NoteDate] >= Forms!FormName.dteFrom AND [NoteDate]<= Forms!FormName.dteTo 
ORDER BY [NoteDate]

Change "FormName" to the name of your form.

[kstyles]

Okay... so this is the same problem I was running into when I tried this by myself before... I keep getting a syntax error...

SELECT [Note Table].[Annual Review Number], Max[Note Table].[Note Date], Min[Note Table].[Note Date]
FROM [Note Table];
GROUP BY [Note Table].[Annual Review Number]

I changed it up to reflect what I saw on the Baldy web site... and still nothing. What am I missing?

[pbaldy]

Both Min and Max are functions that require parentheses around the field names.

[kstyles]

Okay... I am very lost here... I've tried using the parentheses and I am just not figuring this out! I tried changing the square brakets to parentheses and still get a syntax error message.

SELECT (Max[Note Date]), (Min[Note Date])
FROM [Note Table];
GROUP BY Annual Review Number

In reading the notes I have for SQL the operator is a verb and select is acceptable as the operator. Can you point on what I'm doing incorrect here?

THANKS!!!!

[weekend00]

a little bit changes:

change:
SELECT (Max[Note Date]), (Min[Note Date]) FROM [Note Table]; GROUP BY Annual Review Number
to:
SELECT Max([Note Date]), Min([Note Date]) FROM [Note Table] GROUP BY [Annual Review Number]

Maybe you want to add [Annual Review Number] to the select list like this
SELECT [Annual Review Number], Max([Note Date]), Min([Note Date]) FROM [Note Table] GROUP BY [Annual Review Number]

[kstyles]

Yeah!!!! Thank you - I can see now where the parentheses go! I got it to work!

SELECT Max([Note Table].[Note Date]) AS [MaxOfNote Date], Min([Note Table].[Note Date]) AS [MinOfNote Date], [Note Table].[Annual Review Number]
FROM [Note Table]
GROUP BY [Note Table].[Annual Review Number];


THANKS!!!

[pbaldy]

I assumed you would refer back to the sample SQL I posted, which showed the proper placement. Glad you got it sorted out.

[kstyles]

I tried it that way, but I didn't understand how the square brakets fit into the expression. I got myself confused... but now I'm happy to have it actually work!! I really appreciate the help I receive on here - makes things seem pretty easy once I have some input from everyone!

[pbaldy]

The square brackets are not normally required. They are required in your case because of the inadvisable spaces in your names. If it's not too late, I'd get rid of them; in the long run, they're not worth the trouble.