Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314

    Show Assets currently assigned to a site

    I have an Assets_Trans file, which keeps a history, by date, of Asset assignments to sites. An asset can be assigned to a site for a time and then later, moved to a different site. Each assignment creates a separate Assets_Trans record.

    When I want to show all assets assigned to a site, currently, the form shows all current and historical assets assigned to that site.



    I need it to show only those assets currently assigned to the site. I have produced the following code in an attempt to restrict the display of assets to only the current ones, using ELookUp(), but, as written, it does not provide the needed restriction. I was assuming the query would find only the first (most current) Assets_trans record for an asset and attempt to match against it.

    Code:
    =ELookUp("[Site_ID]","Assets_Trans","[Site_ID] =  " & [Sites].[ID],"[Assets_Trans].[Trans_Date] Desc")
    Click image for larger version. 

Name:	Current Asset Assignments.JPG 
Views:	30 
Size:	54.5 KB 
ID:	34965

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    1st it's not Elookup, it's Dlookup.
    2nd, don't use Dlookup in a query. The query IS the Dlookup when you join to a table.

    Dlookup is for form fields or VB code.

  3. #3
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    HI,
    Actually, ELookUp is a 3rd party function, by Allen Brown http://allenbrowne.com/ser-42.html, which does what DLookUp does, but adding an additional "Order By" parameter. I used it elsewhere to provide a value for a field in a form for the most current value from another table. However, that aside, you are suggesting I am using it incorrectly as shown here.


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

  5. #5
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Or maybe this:
    The search opens a select query (just to see how it works) for the date you enter on the main form and the site currently displayed on the subform.

  6. #6
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    Thanks for your reply. Perhaps a little more clarification is in order. Please refer to my response to davegri below.

  7. #7
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    Thanks for your time in your response. Perhaps a little more clarification is in order. Please review the following attachments.

    The 1st image shows 10 battery assets currently assigned to the Lake Mnt-FS site, 5 of which, are being stored there, but will eventually be installed at another site. When those 5 batteries are moved to the new site, I need for them to disappear from the list of assets at the Lake Mnt-FS site.

    The 2nd image shows one of the batteries has been moved to the Lake Mnt-UC site (for test purposes). It also shows it is part of a group or set of 5 batteries, all destined to be moved from the Lake Mnt-FS site to the Lake Mnt-UC site. So now, when I go back to the Sites form, only 4 of these batteries, which have not been moved, should show up in the list of batteries at the Lake Mnt-FS site.
    Attached Thumbnails Attached Thumbnails Site Assets Form View.JPG   Asset Trans Tracking.JPG  

  8. #8
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Apparently, when you move an asset, a new record is added to the junction table to indicate the new site, but the old site record remains.
    You need to find a way to modify the existing junction record, or delete the existing one when a new one is added for that asset.

    You didn't mention how a user transferred an asset, what form was used and the code to do so, etc
    Also, I find it odd that an asset cannot be individually identified with a serial number or some such. When an asset like a battery is moved, and there is more than one,
    how do you identify which actual physical battery it is?

  9. #9
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    1) The intent of the junction table isto permit keeping a history of status changes to an asset. If not, I would not need the junction table. I could do it all in that asset record, by just replacing the site and custodians without any history. There are more status values than just "Deployed" or "Stored" to a site. There are such statuses as: Assigned, Distributed, Inventoried, On Loan, Ready (for use), Repair, Surplus and Untested. If an asset needs to be repaired, we need to so indicate in a new junction record. In this case, the Custodian would be the person repairing it. After it is repaired, it will receive a new status of "Ready", or might go directly to "Deployed". The important thing is to track all the status changes of an Asset.

    2) The 2nd image is the Asset form with the Assets_Trans sub-form. It is here where all status changes are recorded.

    3) Many of these assets were deployed before we began to inventory them. You will note that these assets have a column for the Asset Tag BarCode number. If you look at the asset record in either image, you will also see a field for recording the asset S/N or its MAC address. Since this db is newly developed, these assets have been added to the db before I had a chance to affix asset tags. That will happen shortly, when we go to each site and inventory all the assets. But, I need to get this part of the database working correctly, first, to facilitate that inventory process.

    4) What I need, is to get, for example, the batteries currently being stored at the Lake Mnt-FS site, to disappear from that site, when they are moved to the new site, without deleting the associated junction record. I need to somehow, be able to do like I tried to do with the ELookUp() function, and retrieve the most current junction record for each asset and show only those assets which Site_ID matches the ID of the site being viewed, as in image 1 of my earlier post.

    I hope this helps.

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You may also get some ideas from this older thread or
    from the links identified at the bottom of the thread under Similar Threads

  11. #11
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    On your Sites tab, can't you just filter on max value of [Status Date] to see only latest date?
    We don't know what the recordsource for the form is, so don't know how [Status Date] ties in or where it comes from.
    The Trans_Date in the junction table is probably the date that needs to be checked to get the latest record from the junction table.

  12. #12
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    Thanks. That gives me some ideas. Your example is from the asset perspective. I will need to modify the query to show all assets at a certain sites, based on their most recent transaction file record. Instead of comparing asset ids, I need to compare site ids. I'll give it a try. I may need to wait a couple of days, though, as we are on vacation and preparing for a trip back home tomorrow.

  13. #13
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    @WCStarks

    I moved your last post to this thread. It is relevant to this problem in this thread. The other thread -the one I referenced - had a similar (in concept) issue and it has been solved.
    Good luck with your project.

  14. #14
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    1) I was trying to that with the apparent misuse of the ELookUp() function to find the most current Assets_trans record for the asset. You are probably correct. I probably need to just do a max on the date. I'll give that a try later, as I am getting ready for a trip back home tomorrow.

    2) The Status Date on the form is the Trans_Date field in the Assets_Trans junction table. Trans is short for Transaction, which is how I think of it, maintaining transactions between the assets and the sites, as well as the members. I changed what I wanted the date label to say after creating the table, causing a disconnect between the label and the field name in the table.

    3) So, yes, the trans_date field is the one I need to get the most current date from.

    4) Under the Sites tab, the Site Assets form gets its info primarily from the Assets_Trans table, to gather the assets associated with that site, augmented with data from the Assets table to let us know which asset we are referencing.

  15. #15
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    Thanks. I got confused for a bit.

    How do posters get classified. I am not sure I would call myself a Competent Performer.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 03-17-2017, 11:52 AM
  2. Form showing employee's assigned assets
    By Voodeux2014 in forum Forms
    Replies: 4
    Last Post: 11-06-2014, 11:20 AM
  3. One to Many Assets Database
    By joeyrego in forum Database Design
    Replies: 6
    Last Post: 04-18-2014, 12:44 PM
  4. Replies: 5
    Last Post: 08-20-2012, 11:16 AM
  5. WorthIt fixed assets
    By andres179 in forum Access
    Replies: 1
    Last Post: 05-29-2012, 11:09 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