How to Select the Latest Dated Record?

I don't know SQL and I have searched high and low through Help, but I
can't figure this out...

I have a table that uses a Notes subform in which the users have
entered many notes, each with the date the note was entered. I want to
write a query that selects ONLY the most recent note. What is the
expression I should use in the query to get what I want? THANK YOU!


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

On Mon, 3 May 2004 17:26:03 -0500, Shaken_Bake
wrote:


I don't know SQL and I have searched high and low through Help, but I
can't figure this out...

I have a table that uses a Notes subform in which the users have
entered many notes, each with the date the note was entered. I want to
write a query that selects ONLY the most recent note. What is the
expression I should use in the query to get what I want? THANK YOU!

What's the structure of the table (your Subform doesn't contain any
data - it's just a tool to get data into a table)? Are the dates
entered as part of the note, or is there a separate Date/Time field?

If the latter, use a criterion like

=DMax("[datefield]", "[NotesTableName]", optional criteria)

where the criteria select the subset of notes relevant to the current
mainform record - for instance

"[ForeignKeyField] = " & Forms!mainform!txtID


John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Hi Sharen,

Here are a couple of ways:

SELECT TOP 1 TheDate, TheNote
FROM tblNotes
ORDER BY TheDate DESC;


SELECT TheDate, TheNote
FROM tblNotes
WHERE TheDate = DMax("TheDate", "tblNotes");


On Mon, 3 May 2004 17:26:03 -0500, Shaken_Bake
wrote:


I don't know SQL and I have searched high and low through Help, but I
can't figure this out...

I have a table that uses a Notes subform in which the users have
entered many notes, each with the date the note was entered. I want to
write a query that selects ONLY the most recent note. What is the
expression I should use in the query to get what I want? THANK YOU!


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Hi John and John,

Thanks so much for replying. For clarification, I have a separate Main
Table and another tables with the Notes (joined by a primary key.) The
Notes table has separate notes and date/time fields. I want the Query
results to return a number of fields from both tables, but only giving
me records which contain only the latest dated note. I use the Design
view to create my queries; is there an expression that I can use in the
Criteria line of the Notes/Date column of the Query design (=DMax by
itself didn't work). Thanks again (and what's up with the double
entries of everyone's reply?)


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

On Wed, 5 May 2004 13:34:05 -0500, Shaken_Bake
wrote:

I use the Design
view to create my queries; is there an expression that I can use in the
Criteria line of the Notes/Date column of the Query design (=DMax by
itself didn't work). Thanks again (and what's up with the double
entries of everyone's reply?)

DMax() is a *function* with three arguments: the name of a field; the
name of a table or a query; and an optional criteria string. You can't
just use =DMax - you need to call DMax with the appropriate values for
your table and your query. Since I don't know any of the table or
fieldnames in your database, I can't write the DMax() expression for
you!

It would be SOMETHING LIKE (again, using your table and fieldnames)

=DMax("[Notes/Date]", "[Notes]", "[IDfield] = " & IDField)


John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public