How to Select the Latest Dated Record?

  • Thread starter Thread starter Shaken_Bake
  • Start date Start date
S

Shaken_Bake

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

I have a table that uses a Notes subform in which the users hav
entered many notes, each with the date the note was entered. I want t
write a query that selects ONLY the most recent note. What is th
expression I should use in the query to get what I want? THANK YOU
 
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
 
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");
 
Hi John and John,

Thanks so much for replying. For clarification, I have a separate Mai
Table and another tables with the Notes (joined by a primary key.) Th
Notes table has separate notes and date/time fields. I want the Quer
results to return a number of fields from both tables, but only givin
me records which contain only the latest dated note. I use the Desig
view to create my queries; is there an expression that I can use in th
Criteria line of the Notes/Date column of the Query design (=DMax b
itself didn't work). Thanks again (and what's up with the doubl
entries of everyone's reply?
 
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)
 
Back
Top