Results 1 to 10 of 10
  1. #1
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281

    How to show only the most recent record in userform

    Greetings All...

    Here's your setUp:

    I am using a continuous, bound userform

    Each day the table the form is bound to receives a new set of records to be worked, reconciled then archived to another table

    The issue arises when some records can't be worked they remain in the table, and in the system as not reconciled, thus when the next day's

    batch of records is run those records unworked from the previous day are uploaded to the table again

    with the only difference being the new record will have a current BatchID which is equal to a date/time stamp.


    Thus, what I'm trying to achieve is to have the form (table) show only the most recent of a batch of records.

    I have, as always, tried many avenues to solve, however the below SQL Query, which uses COUNT criteria for everything except the PK and then I use the MAX for the BatchID
    while it works in the query window - the COUNT Criteria causes NAME errors in the form


    Code:
    SELECT Count(TblOOTDetail_New.DaysAgedWhenClosed) AS CountOfDaysAgedWhenClosed, TblOOTDetail_New.TrackNum, TblOOTDetail_New.UIN_MTF_OrderNumb, Count(TblOOTDetail_New.RecDate) AS CountOfRecDate, Count(TblOOTDetail_New.AllInfoRecDt) AS CountOfAllInfoRecDt, Count(TblOOTDetail_New.DateClosed) AS CountOfDateClosed, Max(TblOOTDetail_New.BatchID) AS MaxOfBatchID, Count(TblOOTDetail_New.DateFirstKeyed) AS CountOfDateFirstKeyed, Count(TblOOTDetail_New.OOTReasonCode) AS CountOfOOTReasonCode, Count(TblOOTDetail_New.DipReason) AS CountOfDipReason, Count(TblOOTDetail_New.ResearcherComments) AS CountOfResearcherComments, Count(TblOOTDetail_New.ResearcherIntls) AS CountOfResearcherIntls, Count(TblOOTDetail_New.AssigndToAssociate) AS CountOfAssigndToAssociate, Count(TblOOTDetail_New.AssigndToDepartment) AS CountOfAssigndToDepartment, Count(TblOOTDetail_New.ManagerComments) AS CountOfManagerComments, Count(TblOOTDetail_New.Program) AS CountOfProgram, Count(TblOOTDetail_New.Researched) AS CountOfResearched, Count(TblOOTDetail_New.EmailSent) AS CountOfEmailSent, Count(TblOOTDetail_New.RecrdID) AS CountOfRecrdID
    FROM TblOOTDetail_New
    GROUP BY TblOOTDetail_New.TrackNum, TblOOTDetail_New.UIN_MTF_OrderNumb;
    I'm not sure if a Truncate is the way to go or if there is a better way to do this I'm clearly not thinking about

    Thanks you guys...

    RT91

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    That query would not be an editable recordset.

    Show the code that opens this form. Should be able to use DMax domain aggregate funtion to find the most recent BatchID and apply filter when opening form.
    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.

  3. #3
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    Hey June...

    Yeah, as it turned out that query is, well, completely dysfunctional...

    There is no open/on load code on the form as it opens showing all the records in the table

    The user simply clicks a button to filter the 'needing to be worked' records in the form

    So, I took your idea and placed the following in the click event of that button
    Code:
    Forms("frmOOTTracking_New").Filter = DMax("[BatchID]", "[TblOOTDetail_New]", "OOTReasonCode Is Null")
    This pulls the following Syntax Error: (missing operator) in query expression '11/1/2018 5:44:02 AM'

    I've tried with/without the brackets, using the pound sign #, and while those efforts change slightly the error I'm getting

    I am nonetheless still getting an error

  4. #4
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    Update...

    The following works insofar as it does not error and does produce results - just not the result I need
    Code:
    SELECT TblOOTDetail_New.DaysAgedWhenClosed, TblOOTDetail_New.TrackNum, TblOOTDetail_New.UIN_MTF_OrderNumb, TblOOTDetail_New.RecDate, TblOOTDetail_New.AllInfoRecDt, TblOOTDetail_New.DateClosed, TblOOTDetail_New.BatchID, TblOOTDetail_New.DateFirstKeyed, TblOOTDetail_New.OOTReasonCode, TblOOTDetail_New.DipReason, TblOOTDetail_New.ResearcherComments, TblOOTDetail_New.ResearcherIntls, TblOOTDetail_New.AssigndToAssociate, TblOOTDetail_New.AssigndToDepartment, TblOOTDetail_New.ManagerComments, TblOOTDetail_New.Program, TblOOTDetail_New.Researched, TblOOTDetail_New.EmailSent, TblOOTDetail_New.RecrdID
    FROM TblOOTDetail_New
    WHERE (((TblOOTDetail_New.BatchID)=DMax("[BatchID]","[TblOOTDetail_New]","[TblOOTDetail_New]![OOTReasonCode] Is Null")));
    This will pull back all the records with the most recent BatchID (DMax) where the value in the OOTReasonCode field is Null

    What I need are all the records with Null in the OOTReasonCode field and where those records have a duplicate TrackNum pull only the most recent of those duplicate records

    I hope I am explaining this so it makes sense...

  5. #5
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    At the end of my rope... I'm fairly convinced at this point Access can't do what I need to do.

    Which, in simple context seems like Access should be able to do this all day.

    Query or Filter a table to first show all the records which have Null in one field. Then, as a second filter, if any of those records have a duplicate value in another field show only the 'most recent' of those duplicate records

    So, I'm abandoning the hope Access can do that, so my question now becomes, is there any way Access can accomplish this through other methods? Or, would the best method to see only single records is to simply delete the duplicates

    Thanks as always for any guidance on this one...

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    So a batch can have duplicate TrackNum and records in a single batch will not all have same date? Why are there duplicates?

    I am sure there is a way to do it without deleting. I expect will be complicated and probably involve DCount and an IIf().

    Unfortunately I will not be near Access for another week.
    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.

  7. #7
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    Thanks for sticking with this June...

    Individual batches themselves do not have Dups - What happens is, when a record is not reconciled from the previous day

    it stays on the report and then are rolled up again the next day - thus the record will be the same but the BatchID will reflect a new timestamp (today's date)

    So you will have two identical records with different timestamps...

    Hmmm, if I set the TrackNum Indexed Field to Yes (No Duplicates) would that throw an error? or would it just quietly disallow uploading another record with the same TrackNum?

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    PMFJI,

    Quote Originally Posted by RunTime91 View Post
    Each day the table the form is bound to receives a new set of records to be worked, reconciled then archived to another table. The issue arises when some records can't be worked they remain in the table, and in the system as not reconciled, thus when the next day's batch of records is run those records unworked from the previous day are uploaded to the table again with the only difference being the new record will have a current BatchID which is equal to a date/time stamp.


    Thus, what I'm trying to achieve is to have the form (table) show only the most recent of a batch of records.
    Do all of the current days have the same time stamp? ie all of the records have 11/1/2018 5:44:02 AM in the field "BatchID"?


    How are the 'current days' records being uploaded (receives a new set of records)?

    If the previous unreconciled records are included in today's import, why couldn't you just delete all if the records in the import table before the new records are imported?

  9. #9
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    Hey Steve ~ Thanks for chiming in...

    The records being worked are imported into a local table from a much larger report that is run each morning.

    Any record not meeting certain criteria and has Null in the Reconciled field is then parsed out and sent to a research team

    Once they research and reconcile the record the record is tagged as 'reconciled' and removed from the local table they are working from as well as tagged as reconciled in the main table

    What happens is, any record not reconciled by the research team remains both in their local table and still shows as unreconciled in the main table

    Thus, when the next days main report is run any new unreconciled record and any unreconciled record from previous days are imported [back] into the local table of the research team with today's timestamp

    Which can cause duplicate records with different timestamps

    And yes, to answer your timestamp question, all records being imported from the main report will have today's timestamp

    As to your solution of simply deleting and resubmitting - that wasn't an option when this UI was developed - I'm working with the owner of the data to revisit that option.

    But as of now there seems to be a business need to track the unreconciled records...I just need to find a way for the research team to see only the most recent versions of the unreconciled records

    And man, I hope that helps to clarify my dilemma rather than confuse the issue even more

    Thanks Steve

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Might be simpler than I thought. Try additional filter criteria:

    AND [datefield]=DMax(“datefield”, “table”, “tracknum=‘“ & [tracknum] & “‘“)

    If datefield has time component, will need to extract only date part.

    If tracknum is a number type, remove apostrophe delimiters. If tracknum is not unique, will have to include batchID in the DMax criteria in the same manner as tracknum.

    Be aware domain aggregate functions in query can cause slow performance.
    Last edited by June7; 11-09-2018 at 09:14 PM.

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

Similar Threads

  1. Replies: 4
    Last Post: 08-27-2017, 08:21 AM
  2. Replies: 2
    Last Post: 07-28-2017, 12:08 PM
  3. Replies: 17
    Last Post: 07-21-2017, 01:24 AM
  4. Replies: 6
    Last Post: 02-19-2017, 12:21 PM
  5. Replies: 1
    Last Post: 06-09-2013, 09:16 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