Page 2 of 2 FirstFirst 12
Results 16 to 28 of 28
  1. #16
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    Well, that didn't work out quite as expected. All it did was return only the 5 batteries stored at the site, and none of the other assets.
    I entered this expression in Design view as a criteria for control, Trans_Date:


    Code:
    (Select Max(Assets_Trans.Trans_date) from Assets_Trans where Assets_Trans!Site_ID = Sites!ID)
    That produced the following sql
    Code:
    SELECT Assets.Category, Assets.Make, Assets.Model, Assets_Trans.Trans_Type, Assets_Trans.Trans_Date, Assets.BarCode, Assets_Trans.Asset, Assets.Type, Assets_Trans.Custodian, Assets.Group, Sites.ID, Assets_Trans.Site_ID, Assets.ID, Assets_Trans.Asset_ID
    FROM Sites INNER JOIN (Assets INNER JOIN Assets_Trans ON Assets.ID = Assets_Trans.Asset_ID) ON Sites.ID = Assets_Trans.Site_ID
    WHERE (((Assets_Trans.Trans_Date)=(Select Max(Assets_Trans.Trans_date) from Assets_Trans where Assets_Trans!Site_ID = Sites!ID)))
    ORDER BY Assets.Category, Assets.Make, Assets.Model, Assets_Trans.Trans_Date DESC;

  2. #17
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Yes, an unforeseen result.
    Maybe a strong SQL person could suggest how to retrieve from a Junction table that has many equal FK pairs and containing a date, the max date for each equal pair.
    Given that, it should be easy to restrict the FK for a single site.

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

  4. #19
    Join Date
    Apr 2017
    Posts
    1,673
    I have quite similar database for IT devices. The main difference is, that all transactions are registered for devices, not for sites (in my case for users, site/department is determined for user, and has it's own history). So I don't have the problem like yours with my data entry form (devices form with device users form as subform) - having whole history of device is OK for me. When i need a list of devices for certain user, I have a report for this.

    In your case the easiest way to have only currently allocated devices displayed would be an additional field in transactions table (e.g. IsCurrent). Whenever you add a transaction for asset, some control event must run update query for all transaction entries for this asset, and set IsCurrent to 1 for current (latest entry with date <= as current date, or simply latest entry when entering future dates is excluded), and 0 for all other entries for this asset. In case the entry of future transactions is allowed, you also have tu run the update query on Open event of main form for all assets, to ensure IsCurrent is up to date whenever you open the app.
    For transactions subform, you set the filter "IsCurrent = 1), and it's all you need.

    To get the current entry (transaction ID) for asset, you my use an UDF (like IsValid() the code of which I posted in one of forums a month or two back), or you create a stored query like
    Code:
    qCurrentTransactionDates = SELECT Asset_ID, MAX(Trans_Date) AS Trans_Date FROM Asset_Trans GROUP BY Asset_ID WHERE Trans_Date <= Date()
    and then you get all current transactions with stored query like
    Code:
    qCurrentTransactions = SELECT at.* FROM Asset_Trans at INNER JOIN qCurrentTransactionDate ctd ON ctd.Asset_ID = at.Asset_ID AND ctd.Trans_Date = ctd.Trans_Date

  5. #20
    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 agree with Paul - suggest you post a copy of the database so we can see the issue in context.

  6. #21
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    Paul, I will try your 2 query suggestion when I get back home. If I am still having problems, I'll post a copy of the db for review.

  7. #22
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    Paul, I tried the two query suggestion without success. The Assets_Trans table, among other things, relates Assets with Sites. I need this query to return only one entry for each asset (the most current entry). I have one test Asset with two Assets_Trans records, one with a date in 2017 and another with a date in 2018. The Asset_ID is 162. Both entries still show up in my query.

    I tried just the Max and First of the date field and with and without adding First of the Asset_ID. but Asset 162 shows up for both dates. I am missing some fundamental aspect of this issue.

    Click image for larger version. 

Name:	Return current record.JPG 
Views:	21 
Size:	34.1 KB 
ID:	35007Click image for larger version. 

Name:	Assets_Trans Current Query.JPG 
Views:	21 
Size:	54.2 KB 
ID:	35008

  8. #23
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Where's the other query?

    It would be easier with a sample db. The first query should only have 2 fields, grouping on the asset number and max date. Then you join that query to the table on those 2 fields.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #24
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    I will prepare a copy of the db without personal information for tomorrow. In the mean time, I thought the second query would be the original query modified to use this new query instead of Assets_Trans directly. So I attempted to get the new query to limit the entries in Assets_Trans to just one entry per asset and then bring it into the original query in place of the Assets_Trans table. Since I needed many of the other fields in the Assets_Trans, I kept them in this new query to pass along. But, I guess, you are saying I can't do that. So, do I need two queries below the query supporting the form? I guess I'll need to review your previous post more carefully.

    Below, is the image of the query, with the critical elements showing, supporting the Site Assets form. I had planned to replace the Assets_Trans table with the new query. I was assuming that the query will have reduced the Assets_Trans records to only one for each asset.

    Click image for larger version. 

Name:	Assets_Trans_Sites query builder.JPG 
Views:	22 
Size:	49.8 KB 
ID:	35011

  10. #25
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    I reduced the query to the two fields as you indicated and it worked. Now, I need to figure out how to get the other fields in Assets_Trans passed to the query supporting the form, which I am struggling with how to do it. Without an Assets_Trans ID, I don't know which record is which.

  11. #26
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    Purpose: When an asset is moved from one site to another, it should also disappear from the asset list in the first site in the form, while keeping a history of asset statuses in the Assets_Trans table.
    The first query restricts the data set from the Assets_Trans table, and the second query retrieves the full records from Assets_Trans for the reduced data set.

    I should have followed your instructions more fully. Where I didn't have an Assets_Trans.ID in the first query, I didn't see how I could use the result. But, with blind faith, I tried joining as you suggested, and it worked. I have modified the Form Query to use this second query as its data source in place of the Assets_Trans table, and now it does what I need it to do. At first, I wasn't sure why it worked, but after thinking it through, I understand. I also understand that this method assumes there is only one such transaction record per day. I hadn't intended to enforce that constraint, but I must do so now. Issue resolved.

    The solution:
    Code:
    
    Finding the most recent record by date, in a junction table for use in a Form
    Example using the Assets_Trans table, which provides a history of status changes to an asset:
    Find the current Asset status for each Asset.
    
    
    1. Create a query, including only two fields from Assets_Trans
    Asset_ID, and Trans_Date
    1. Group by Asset_ID
    2. Max(Trans_Date) Descending [I suppose one could also use First instead of Max and get the same result.]
    3. Create a new query to Join this query with the Assets_Trans table on those two fields
    This retrieves the full records for the reduced data set from the Asset_Trans table.
    1. Use this second query in the form query, in place of the Assets_Trans table
    Note: This method constrains creating only one status record in the Assets_Trans table per day. Note: To find the earliest status for an asset, just replace the Descending sort order to Ascending.
    Thank you Paul
    Last edited by WCStarks; 08-08-2018 at 09:40 AM.

  12. #27
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    This solution has an interesting side-affect. Any asset without a status date does not get included in the result set. I had many status dates which were unknown at this time. I had to go in and assign temporary status dates to those assets in order to get them into the result set.

  13. #28
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    Paul,
    There is apparently another unanticipated side-affect of this solution. None of the fields in the Site Assets form (the subject of this solution) can be modified. This, even though their properties are all enabled. This form combines fields from both the Assets table and the Assets_Trans table. Is this suppose to happen? I can edit the assets in the sister Asset form below, but I have no where to edit the Status, Date and Custodian from the Assets_Trans table.

Page 2 of 2 FirstFirst 12
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