If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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/ |
#2
|
|||
|
|||
How to Select the Latest Dated Record?
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 |
#3
|
|||
|
|||
How to Select the Latest Dated Record?
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. |
#4
|
|||
|
|||
How to Select the Latest Dated Record?
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/ |
#5
|
|||
|
|||
How to Select the Latest Dated Record?
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 |
Thread Tools | |
Display Modes | |
|
|