Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    rohinikrishna1987 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2020
    Posts
    36

    Find missing period (date)

    Sir I have some date series in to field, start date and end date, like 1/03/2020 - 31/03/2020 like that, and countiniously
    next is 01/04/2020 to some other date...But some cases next start date may not continues, it may 05/04/20 instead of 01/04/2020. I want to find out the missing period, ie, 01/04/2020 to 04/04/2020 (missing period). How can i find out this missing period in msaccess data base.Pls help me...



    Start Date End date
    1/03/2020 31/03/2020
    05/04/2020 30/06/2020 How to find the missing period, ie, 01/04/2020 to 04/04/2020 by using query or other any method....I am a beginner
    01/07/2020 31/12/2020

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I have a tDate table that has all the dates.
    join your table to this table using OUTER join,
    bring down the date field from both tables into the query grid,
    dbl-click the join line ,set the tDates tbl to SHOW ALL RECORDS,
    run query ,the null values from your table are the missing ones.

    in the query ,you can set the criteria under your tbl date field to null to see only the missing records.

  3. #3
    rohinikrishna1987 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2020
    Posts
    36
    Sir how i get your all date table

  4. #4
    rohinikrishna1987 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2020
    Posts
    36
    Quote Originally Posted by ranman256 View Post
    I have a tDate table that has all the dates.
    join your table to this table using OUTER join,
    bring down the date field from both tables into the query grid,
    dbl-click the join line ,set the tDates tbl to SHOW ALL RECORDS,
    run query ,the null values from your table are the missing ones.

    in the query ,you can set the criteria under your tbl date field to null to see only the missing records.
    sir pls send me the said all date table to me via my email: avinanair@gmail.com

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by rohinikrishna1987 View Post
    Sir how i get your all date table
    This is something you can easily do yourself. Create a table, maybe call it "tblAllDates", with a field named "AllDates". Write a little VBA code to insert the dates from the date you want to begin with (Start Date) to the end date.
    However, from what little you wrote in the first post, I do not think having a table with All Dates will help you.


    What is your table design? Do you have a field for "StartDate" and a field for "EndDate"?

    Your example
    Start Date
    End Date
    1/March/2020 31/March/2020
    (missing period)
    5/May/2020 30/June/2020
    1/July/2020 31/Dec/2020

    the missing date period is 1/April/2020 through 4/April/2020. I think you will have to write some VBA code to compare the ending date of the current record with the beginning date of the next record.
    You didn't say what you want to happen if there is a missing period (as in above).

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you can adapt this query to provide your missing dates - from your example data it returns MissingFrom=1/4/2020, MissingTo 4/4/2020

    Code:
    SELECT S.MissingFrom, Min([SDate]-1) AS MissingTo
    FROM (SELECT S.EDate+1 AS MissingFrom
    FROM tblDates AS S LEFT JOIN tblDates AS E ON S.EDate+1= E.SDate
    WHERE (((E.sDate) Is Null)))  AS S, tblDates
    WHERE (((tblDates.SDate)>=[MissingFrom]))
    GROUP BY S.MissingFrom;

  7. #7
    rohinikrishna1987 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2020
    Posts
    36
    Sir I have 2 tables. One is incumbent table which have fields that, Name, Designation, DoB, etc. Next table is ACR table....that is Annual Confidential Report table... in which fields are Date periods...that is the periods assessed by their superior officers...so table has field as following....ACR start date, ACR Endt date, Grade A, Grade B, Grade C etc....

    Both tables are connected with one to many relation...that is on incumbent has many ACR periods...I want to find out the missing period of ACR of each officer....that is why I posted

  8. #8
    rohinikrishna1987 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2020
    Posts
    36
    sir, if table name is 'ACR' filed names are STDT and ENDT....Pls convert your query....bcause i could not transform the same...

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if table name is 'ACR' filed names are STDT and ENDT.
    Code:
    SELECT S.MissingFrom, Min([STDT]-1) AS MissingTo
    FROM (SELECT S.ENDDT+1 AS MissingFrom
    FROM ACR AS S LEFT JOIN ACR AS E ON S.ENDT+1= E.STDT
    WHERE (((E.STDT) Is Null)))  AS S, ACR
    WHERE (((ACR.STDT)>=[MissingFrom]))
    GROUP BY S.MissingFrom
    For the future - if you need a detailed answer, provide all the details.

    I suspect the above will still not work for you because you have now said

    Both tables are connected with one to many relation...that is on incumbent has many ACR periods...I want to find out the missing period of ACR of each officer....that is why I posted
    the solution I provided was to answer this request

    have some date series in to field, start date and end date, like 1/03/2020 - 31/03/2020 like that, and countiniously
    next is 01/04/2020 to some other date...But some cases next start date may not continues, it may 05/04/20 instead of 01/04/2020
    No mention there about per officer. And your revised requirement still does not mention the required field names so cannot really provide an updated solution. Hopefully you can work it out for yourself.

    For the future, if you are going to provide more information, also confirm that date ranges do not overlap - e.g.

    STDT....ENDT
    1/3/20...31/3/20
    20/3/20..30/4/20

    And that fields are always populated (e.g. no blank end dates)

    You also say
    the missing period of ACR of each officer
    does this mean there is always only one missing period per officer? Or might some officers have no missing periods or multiple missing periods? What do you want to happen if there are no missing periods? Or there are multiple missing periods?

    It would be better if rather than trying to describe what you need, provide some example data that covers all possibilities - and also provide the result you want from that data.

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by rohinikrishna1987 View Post
    One is incumbent table which have fields that, Name, Designation, DoB, etc. Next table is ACR table....that is Annual Confidential Report table... in which fields are Date periods...that is the periods assessed by their superior officers...so table has field as following....ACR start date, ACR Endt date, Grade A, Grade B, Grade C etc....

    Both tables are connected with one to many relation...that is on incumbent has many ACR periods...I want to find out the missing period of ACR of each officer....that is why I posted
    You only have 1 field for name? Much better if you have two fields : "FirstName" and "Surname". Don't use "Name" - it is not very descriptive (name of what?) and it is a reserved word in Access.
    The ACR table appears to have repeating fields (Grade A, Grade B, Grade C, etc) - this is not a normalized table design. It looks like a spreadsheet converted into a table.
    There are also spaces in the object names (not good)


    In addition to what Ajax said, it would also be very helpful if you could post an image of the relationship window or post the dB.

  11. #11
    rohinikrishna1987 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2020
    Posts
    36
    Thanks sir,and appreciated your care...and sorry for taking ur valuable time........Any way i will explain my data base as far as possible

    Table1-[PERSONAL] Fields are...1.PLID (Auto),2.SNLST (num),3.NAM (Text),4.DOB (date)
    Table2-[ACR] Fields are...1.ID (Auto),2.PLID (num),3.STDT (date),4.ENDT (Date),4.GRADE (Text)
    Table1's PLID(Auto) field is joined with Table2's PLID (num) field with one to many relation.
    We have to enter All details of Table1 through data entry and Table2s fields are also to be enter
    So there is no chance of overlaping period like u say...but the all start date and end date should in calender year...1/1/2019 to 15/6/2019
    and 16/6/2019 to 31/12/2019..not permitted to overlap next calender year, ie...1/12/2019 to 31/3/2020 is not permitted. All ACR datas will be submitted by concerned officers to my office.
    Due to huge records, while entering this start date and end date of ACR, we couldnot listen the missing period..More over they may submit the ACR period in not chronological order, ie they may submit one ACR for 1/1/2011 to 31/3/2011 and nex maybe 1/1/2012 to 31/12/2012 after long period they may submit the 1/4/2011 to 31/12/2011. This have to be find out.

    Like u said, their may be multiple missing period for one officer or may some officer have no missing period.
    Kindly decribe me, how i can find out the missing periods from these datas....Thanku very much

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Kindly decribe me, how i can find out the missing periods from these datas
    when you have provided the information as requested

    provide some example data that covers all possibilities - and also provide the result you want from that data.
    very helpful if you could post an image of the relationship window or post the dB.
    What do you want to happen if there are no missing periods? Or there are multiple missing periods?

    You know your business, we don't. The descriptions you provide are unclear

    We have to enter All details of Table1 through data entry and Table2s fields are also to be enter
    So there is no chance of overlaping period like u say
    I don't see how this statement can be true

  13. #13
    rohinikrishna1987 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2020
    Posts
    36
    Thanks, I have posted screen shot of some of my tables, relations etc...I want to get report like the last image (Screen 6)...I obtained the same by altering the project completely...ie, Instead of one to many relation between the tables, I join the tables one-one relation...and the second table include field for Start date1, End date1, Start date2, End date2 and so on...This is very repetitive and not the right one...Hence I want to create the same report as in my first project ie, with two tables with one to many relation .....(Screen 6) could not attach...I will send the same in next reply
    Attached Thumbnails Attached Thumbnails screen1.png   scree2.png   screen3.png   screen4.png   screen5.png  


  14. #14
    rohinikrishna1987 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2020
    Posts
    36
    Screen 6, which is the report like one, I want
    Attached Thumbnails Attached Thumbnails screen6.png  

  15. #15
    rohinikrishna1987 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2020
    Posts
    36
    kindly look in to the reply

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

Similar Threads

  1. Criteria to specify date period
    By Toble in forum Queries
    Replies: 2
    Last Post: 07-08-2015, 11:17 PM
  2. To find Month and Days in given date period
    By waqas in forum Programming
    Replies: 1
    Last Post: 02-12-2013, 02:50 PM
  3. Alert on Existing Date Period
    By waqas in forum Access
    Replies: 5
    Last Post: 02-03-2013, 10:38 PM
  4. Getting Tax Period from current date
    By crxftw in forum Forms
    Replies: 3
    Last Post: 07-09-2011, 07:12 AM
  5. How to find missing date query
    By twhite in forum Queries
    Replies: 8
    Last Post: 09-02-2010, 02:42 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