Results 1 to 3 of 3
  1. #1
    Shredder is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2018
    Posts
    1

    Date issues on equpment sent out

    I have a database where I track the equipment going out for repair and calibration. I have a query of the repair table (Keying in on date) which shows me all the records of repairs ever completed. All equipment has to go back for repair and calibration within an 18 month time period. However I am not sure what the best way to show me only the newest date sent out for each serial number. I have it sorted by date so I basically just need it to show me the newest date for each piece of equipment (serial number) so I can see when the last time the serial number went out.



    I have the following fields.

    ID, equipment number, Serial number, Sentdate

    The example below is for equipment# 5 which is serial number 1234567890.

    ID Equip# Serial Sent date

    1 5 1234567890 01/01/14

    The next time it is sent out.

    114 5 1234567890 06/01/16

    and again the next time it goes out

    223 5 1234567890 11/20/18

    I am sorting by date so these would be randomly in the query.

    This might be something simple but I am having a hard time figuring it out being new to access. Any help would be greatly appreciated and hopefully this makes sense.

  2. #2
    Gicu's Avatar
    Gicu is online now VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Just create a totals query and GroupBy the Equip# and show Max of SentOutDate

    SELECT [YourTableName].[Equip#], [YourTableName].[Serial],Max([SentDate]) AS LastSentDate
    FROM [YourTableName]
    GROUP BY YourTableName.[Equip#],[YourTableName].[Serial];

    Cheers,
    Vlad

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Shredder View Post
    <snip>
    I have the following fields.

    ID, equipment number, Serial number, Sentdate

    ID Equip# Serial Sent date

    <snip>
    Both of these example field names should be fix before you get any further.
    The hash mark ("#") in the field names "Equip#", is a date delimiter and a reserved symbol and shouldn't be used in object names.
    If you have "equipment number" as a field name, it has a space in the name. Should NEVER use spaces in object names.
    Same comment for a field name of "Sent date".




    Other suggestions:
    --------------------
    Use only letters and numbers (exception is the underscore) for object names.
    Do not begin object names with a number.
    Do not use spaces, punctuation or special characters in object names.
    Do not use Look up FIELDS, multi-value fields or calculated fields in tables.

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

Similar Threads

  1. VBA Sql date issues
    By Larryg in forum Queries
    Replies: 7
    Last Post: 04-02-2017, 02:29 PM
  2. date/time field giving me issues
    By bchi99 in forum Queries
    Replies: 1
    Last Post: 11-04-2014, 04:51 PM
  3. Date Range form issues
    By msuguy71 in forum Forms
    Replies: 1
    Last Post: 12-31-2013, 03:32 AM
  4. Date format issues
    By budedwards in forum Queries
    Replies: 14
    Last Post: 03-15-2012, 10:52 AM
  5. Date expression issues
    By QTip in forum Queries
    Replies: 4
    Last Post: 01-06-2011, 03:43 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