Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    12

    Unanswered: New to access requesting help

    Hi,

    I am just new to using access and I am having a bit of difficulty with limiting a search to the details at the last entered date. I have the following table:

    tblTest
    TestID - Autonumber
    Name - text
    Date - Date/Time
    Amount - Number

    I entered the following data" two different names (Bill and Tim) with two different dates and amounts for each name. So 4 records in total.

    when I run a query just projecting the name and date, I retrieve all 4 records. When I select, Max on the date, I only retrieve the most recent two records.

    When I then project the Amount into the query, and keep the max on date, I retrieve all 4 records again! I only want the latest two, with the amount data for those records.

    What do I have to do to achieve this?

    Thanks

    Jeff

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Here is some SQL for you ive struggled to reproduce what you did
    but have done this problem how i would of approached it

    Code:
    SELECT tblTest.Name, tblTest.Date, tblTest.Amount
    FROM tblTest
    WHERE tblTest.Date IN (select Max([Date]) From tblTest);
    Is this any help
    Dave

  3. #3
    Join Date
    Mar 2004
    Posts
    12
    Hi Dave,
    You made it look so easy. Fantastic.

    Thanks
    Jeff

Posting Permissions

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