Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Selecting a record based on the Transaction date (Access XP)

    How can I select a record based on the transaction date of that record being the closest to the current date?

    Thanks,

    Christa

  2. #2
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting a record based on the Transaction date (Access XP)

    Thanks, Hans...

    Yes the transaction date will always on on or before today's date.

    I've tried to put in the select statement you suggested in my query but I'm getting a syntax error.

    This is the statement I'm using:

    (Select Max () from [ECMA1]!<trANSACTION_DATE> )

    I'm doing a query of a query called ECMA1 and the field name in that I'm querying is called TRANSACTION_DATE. Can you see what I'm doing wrong.

    Thanks again for your help.

    Christa

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Selecting a record based on the Transaction date (Access XP)

    Will the transaction date always be on or before today? If so, you can create a query based on the table, with the following expression in the Criteria line for the TransactionDate field. You must substitute the actual table name and field name.

    (SELECT Max([TheDate]) from [tblTransactions])

    If the transaction date can also be in the future, post back.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Selecting a record based on the Transaction date (Access XP)

    Sorry, the browser or the lounge software messed up the expression I posted - apparently TransactionDate in square brackets has a special meaning; I have corrected it by using a different field name in the example. Try this:

    (SELECT Max(TRANSACTION_DATE) FROM [ECMA1])

  5. #5
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting a record based on the Transaction date (Access XP)

    Thanks...I finally got back to this and got it to work...can I now modify this somehow so that it picks the maximum transaction date for each unique ID number in the query? Right now it's just selecting the the ID that has the maximum date.

    Thanks again.

    Christa

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Selecting a record based on the Transaction date (Access XP)

    Create a query based on the ECMA1 table, with ID and TRANSACTION_DATE as fields. Select View | Totals to make it into a totals query. Leave the Total option for ID as Group By, and set it to Max for Transaction_Date. This query will return the max date for each ID. Save this query as qryMaxDate.

    If you want to see the other fields for these records, create a new query based on ECMA1 and qryMaxDate. Join them on the ID field and on TRANSACTION_DATE vs MaxOfTRANSACTION_DATE. Drag the fields you want to see, or *, from ECMA1 to the query design grid.

  7. #7
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting a record based on the Transaction date (Access XP)

    Hans! You are a genius. I can't thank you enough!

    Thanks

    Christa

Posting Permissions

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