Results 1 to 12 of 12
  1. #1
    duramax is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2013
    Posts
    5

    Location of asset and latest record for asset

    I have a database started to track an asset that can go out for repair and back in and then stored. When needed it would be removed from storage and placed in use. I have a serial number for each asset and want a query to pull the latest record for each serial number. This will tell me where each asset is at that time becuase i enter a new record each time the location changes. I have only around 90 serial numbers but hundreds of records for the 90. Any help would be greatly appreciated. Thanks

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,324
    When an activity occurs for an asset, you should record the Date of that activity (and time if necessary).

    Asset 6 placed in storage April 7 2013
    Asset 6 moved to repair shop April 10, 2013
    Asset 6 returned from repair shop April 12, 2013
    Asset 6 placed into use April 13, 2013
    ...

    You have many assets and you have many transaction types/locations.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,324
    When an activity occurs for an asset, you should record the Date of that activity (and time if necessary).

    Asset 6 placed in storage April 7 2013
    Asset 6 moved to repair shop April 10, 2013
    Asset 6 returned from repair shop April 12, 2013
    Asset 6 placed into use April 13, 2013
    ...

    You have many assets and you have many transaction types/locations.

    I posted some pictures and a database that might be useful to you at
    https://www.accessforums.net/forms/h...orm-24504.html

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,324
    Review http://allenbrowne.com/subquery-01.html
    especially the example for Top N records per group

    Another method is to use DLookup as criteria.

    Which latest record do you want? The latest repair? If every asset has a repair record, then pull the record for each asset with the max date, something like.

    SELECT * FROM Repairs WHERE RepairDate=DMax("RepairDate", "Repairs", "SN='" & [SN] & "'");

    If some assets do not have repairs then join Assets table to Repairs and apply the above criteria along with OR Is Null.

    Have you looked at the Lending Library and Asset Tracking database templates?

    http://office.microsoft.com/en-us/te...001225342.aspx

    http://office.microsoft.com/en-us/te...ai:TC010206883|
    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.

  5. #5
    duramax is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2013
    Posts
    5
    Thank you for the quick response. I do have a date and time stamp for each record. There are some many options to arrive at the same thing and I have had a bit of a mental block. I will try the suggestions and look at all the resources listed. Thanks

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,324

  7. #7
    duramax is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2013
    Posts
    5
    Quote Originally Posted by June7 View Post
    Review http://allenbrowne.com/subquery-01.html
    especially the example for Top N records per group

    Another method is to use DLookup as criteria.

    Which latest record do you want? The latest repair? If every asset has a repair record, then pull the record for each asset with the max date, something like.

    SELECT * FROM Repairs WHERE RepairDate=DMax("RepairDate", "Repairs", "SN='" & [SN] & "'");

    If some assets do not have repairs then join Assets table to Repairs and apply the above criteria along with OR Is Null.

    Have you looked at the Lending Library and Asset Tracking database templates?

    http://office.microsoft.com/en-us/te...001225342.aspx

    http://office.microsoft.com/en-us/te...ai:TC010206883|
    I have tried the code above and I am having a tough time getting to work. Here is what I have:
    SELECT RollData.When, RollData.Location, RollData.[Unique Identity], RollData.Commnets
    FROM RollData
    GROUP BY RollData.When, RollData.Location, RollData.[Unique Identity], RollData.Commnets;

    I need to find the latest date in the table for each unique identity. Sorry but this is new to me.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,127
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    duramax is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2013
    Posts
    5
    Can this be completed with using Criteria?

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,127
    A criteria can be added if that's what you mean.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    duramax is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2013
    Posts
    5
    Quote Originally Posted by pbaldy View Post

    This link really helped. I removed all the fields except for the two i needed and the query worked. I then did the join to add back all the fields i need. I think i must have had something wrong in the syntax in the select part of the code. If time permits i will try to figure out but for now I have everyone happy and thanks much for all the help.

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,127
    Glad it helped you.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Asset Management Web Database
    By gemadan96 in forum SharePoint
    Replies: 2
    Last Post: 05-01-2013, 02:36 PM
  2. Inventory/Asset tracking database.
    By russkris in forum Access
    Replies: 7
    Last Post: 04-26-2012, 02:27 PM
  3. Asset Tracking Database
    By wgroenewald in forum Access
    Replies: 3
    Last Post: 02-13-2012, 08:26 PM
  4. Purchase price of asset
    By Ron.Sul in forum Access
    Replies: 4
    Last Post: 09-17-2010, 11:28 PM
  5. Storing asset class Correlations help!
    By tdham in forum Database Design
    Replies: 1
    Last Post: 04-27-2010, 08:33 AM

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