Results 1 to 5 of 5
  1. #1
    Daenex is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jul 2020
    Posts
    3

    How to choose minimum date from a record out of multiple columns


    Hello,


    I am creating a database and new to access. I have been going through the tutorials here to clean up what i have done thus far. However, it seems I have bitten off more than i can chew.
    Please I am asking for your help.

    I want to build a query that finds the minimum date which will now be the new expiry date for a vehicle permit.

    The table has four (4) columns namely SPE, RE, COFE, & IE. These are all dates. from that one record i need to find the earliest date which would now be the new expiry date.

    Permit#(pk) SPE RE COFE IE
    PT-001 5/11/20 10/31/20 8/9/20 3/11/20

    How do I do that?

    Thanks for your help.

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Write an union query to put all dates into one field:

    Select [Permit#], [SPE] as ExpiryDate FROM tblYourTable
    UNION
    Select [Permit#], [RE] as ExpiryDate FROM tblYourTable
    UNION
    Select [Permit#], [COFE] as ExpiryDate FROM tblYourTable
    UNION
    Select [Permit#], [IE] as ExpiryDate FROM tblYourTable;

    Now use this query in a totals query and group by Permit# and select Min for the ExpiryDate.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Daenex is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jul 2020
    Posts
    3

    Thanks

    Quote Originally Posted by Gicu View Post
    Write an union query to put all dates into one field:

    Select [Permit#], [SPE] as ExpiryDate FROM tblYourTable
    UNION
    Select [Permit#], [RE] as ExpiryDate FROM tblYourTable
    UNION
    Select [Permit#], [COFE] as ExpiryDate FROM tblYourTable
    UNION
    Select [Permit#], [IE] as ExpiryDate FROM tblYourTable;

    Now use this query in a totals query and group by Permit# and select Min for the ExpiryDate.

    Cheers,
    I am going to try that right away. Thanks very much. I will give a feedback for sure.

  5. #5
    Daenex is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jul 2020
    Posts
    3
    Thanks for the resource. I will do some reading.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 05-30-2017, 09:05 AM
  2. Replies: 1
    Last Post: 05-29-2013, 04:01 PM
  3. Choose the row with the MOST RECENT date
    By taimysho0 in forum Queries
    Replies: 1
    Last Post: 05-14-2012, 02:35 PM
  4. Choose multiple dates
    By tomclavil in forum Programming
    Replies: 0
    Last Post: 04-07-2012, 11:08 PM
  5. Replies: 1
    Last Post: 04-02-2012, 04:20 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums