Results 1 to 7 of 7
  1. #1
    smg is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Apr 2019
    Posts
    121

    show a termination this period in the next period's query or report

    It may just be getting late in the day because this seems like it should be simple.


    I have an employee who terminated 10/9/2020. He shows up in my current period query because it is between the period from 10/3 through the period to of 10/30. I want him to also show up in the next period query which is period from 10/31 thru period to 11/27. What I would like my query to do is add a field that pulls in the 11/27 date if the term date falls between the prior period dates. I have period table with monthly from and to dates. Termination dates aren't always provided in a timely manner.

    Term Date Period From Period To NextPeriodTo
    10/9/2020 10/3/2020 10/30/2020 11/27/2020

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Wouldn't just going back 60 or so days from the current NextPeriodTo work? Or if you know someone should show up but doesn't, why fix the range instead of having two form controls with calendars to pick a date range?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    smg is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Apr 2019
    Posts
    121
    No that would not work. I need to pull the NextPeriodTo into my query. Here is another example of what might occur. I could receive a term date in Nov for someone that is scheduled to term in Jan. I want the Feb report to pick it up.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    per your thread here

    https://www.accessforums.net/showthr...535#post466535

    as already suggested, use a 'period to date' query

    by continuing with your current basis you will be piling one fix after another to create a really complex mess.

    I suspect you haven't even considered what to do when the data is wrong - such as someone terminating in October is not posted until December.

    Good luck with your project, but I shan't contribute further

  5. #5
    smg is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Apr 2019
    Posts
    121
    I am no longer creating a table of monthly values. I just need to be able to pick up terms up to one month after termination date but still query in period increments.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    No that would not work.
    Well, you lost me as to why and your explanation makes no sense. If you're doing this from a form as you should be and you want to include February then what's stopping you from structuring the query to use the appropriate date value for criteria in any date field in that query? I shall have to review the linked thread to see if you're not following advice from people that I regard as being very smart with Access.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    smg is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Apr 2019
    Posts
    121
    I appreciate and follow advice given here. Previously, under database design, I asked about storing a monthly value and was advised against that as it would best be done by accounting. I took that advice and now just have a cumulative amount. This is a different question just related to termination dates that fall outside of a reporting period, but I am probably asking it incorrectly. My apologies.

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

Similar Threads

  1. Create Time period query
    By RDT0086 in forum Queries
    Replies: 5
    Last Post: 02-12-2016, 12:16 PM
  2. Replies: 32
    Last Post: 03-01-2015, 12:01 PM
  3. Replies: 6
    Last Post: 06-28-2014, 07:45 PM
  4. Replies: 1
    Last Post: 03-27-2014, 06:43 PM
  5. Replies: 6
    Last Post: 10-28-2013, 11:51 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