Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2005
    Posts
    165

    Unanswered: Selecting the last date in a series of dates...

    I have a database where we track certain events on a clients file. In the same table we can have multiple dates representing something important taking place on that date. Is there a way to query that table and display only the last date for each record? So, if I have events on a client's file on 1-1-05, 1-15-05, and 1-30-05, I only want the query to display the last date, or the 1-30-05 in my example. Thank you for any help.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    SELECT TOP 1 SomeDate FROM SomeTable WHERE ( SomeCondition=SomeThingElse) ORDER BY SomeDate DESC;
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have a look at the order & top nnn predicates for SQL

    eg
    select top 1 [column1],[column2],[column3],[column4]
    from [tablename]
    order by [datecolumn] desc


    nto tested but should work.....

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Last Date

    I had a few problems with this not always working but you can try:

    1. In your query, make it a "Totals" query (i.e. select the funny looking E character.)
    2. Drag down your date field.
    3. For the Total: row for that column: change it to "Last".
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178

    Thumbs up

    Quote Originally Posted by pkstormy
    I had a few problems with this not always working but you can try:

    1. In your query, make it a "Totals" query (i.e. select the funny looking E character.)
    2. Drag down your date field.
    3. For the Total: row for that column: change it to "Last".
    I agree, but I would change it to "Max" instead, in case the records aren't sorted on date.

    Also, be sure your dates are in a "Date" data type field. If the data type of the date field is "Text," you need to use the CDate() function, as in CDate([DateField]).

    Your SQL statement might look something like this:

    PARAMTERS [Enter Name] Text;
    SELECT ClientName, ClientInfo, EventDate FROM TableName
    WHERE ClientName = [Enter Name] And EventDate = Max([EventDate])
    GROUP ON ClientName, ClientInfo;

    When you activate the query, the PARAMETER statement will prompt you to enter a client name. (Of course you can have your VBA enter the name programatically; I do it all the time myself.) You will, of course, need to embellish the SQL statement to suit your needs, and I'm assuming you're using Access's built-in QBE design grid.

    Sam
    Last edited by Sam Landy; 02-01-05 at 18:27.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This is the method to use to get the results you want:

    Code:
    Select	YourTable.clientID,
    	YourTable.eventdate
    from	YourTable
    	inner join
    	(Select	clientID,
    		max(eventdate) eventdate
    	from	YourTable) LatestRecords
    	on YourTable.clientID = LatestRecords.clientID
    	and YourTable.eventdate = LatestRecords.eventdate
    You can implement the subquery as a separate view if it is more convenient for you.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •