Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 31
  1. #16
    rohinikrishna1987 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2020
    Posts
    36
    Please reply to me

  2. #17
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    based on the tables provided your query would look like this

    SELECT ACR.PLID, ACR.ACRSTDT, ACR.ACRENDDT, M.MissingFrom, M.MissingTo, [MissingTo]-[MissingFrom]+1 AS DaysMissed
    FROM ACR LEFT JOIN (SELECT S.PLID, S.[ACRSTDT], Max(ACR.[ACRENDDT]+1) AS MissingFrom, S.MissingTo FROM ACR INNER JOIN (SELECT S.PLID, S.[ACRSTDT], S.[ACRSTDT]-1 AS MissingTo FROM ACR AS S LEFT JOIN ACR AS E ON (S.[ACRSTDT]-1=E.[ACRENDDT]) AND (S.PLID = E.PLID) WHERE (((E.[ACRENDDT]) Is Null))) AS S ON [ACR].PLID=S.PLID WHERE (((ACR.[ACRSTDT])<[MissingTo])) GROUP BY S.PLID, S.[ACRSTDT], S.MissingTo) AS M ON (ACR.[ACRSTDT] = M.[ACRSTDT]) AND (ACR.PLID = M.PLID)
    ORDER BY ACR.PLID, ACR.ACRSTDT;

  3. #18
    rohinikrishna1987 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2020
    Posts
    36
    I have applied your query, but following results are obtained...a showed the print screen result as attached. kindly help me to solve the problem
    Attached Thumbnails Attached Thumbnails ajax1.png   ajax2.png   ajax3.png  

  4. #19
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by rohinikrishna1987 View Post
    So there is no chance of overlaping period ...
    When this is the case, then there is no need for end dates! Have start date of period in your table, and you can always calculate the end date for this period:
    You have to calculate minimum start date for PLID where ACRSDT > period start date. When the result is Null (there was no later start dates for this PLID), the period's end date will be today, otherwise it will be the result - 1 day.

  5. #20
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    looks like I included an extra D in ACRENDT

  6. #21
    rohinikrishna1987 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2020
    Posts
    36
    Thank u sir............................................... ...with love, its working.....thank u very much and may keep in touch.....thank u once again

  7. #22
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    happy to help

  8. #23
    rohinikrishna1987 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2020
    Posts
    36
    you are great

  9. #24
    rohinikrishna1987 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2020
    Posts
    36
    A small problem... I have to select each ACR of each employee for ACR data entry or ACR report of a particular employee. This is done with entering criteria basis on PEN ( employee number) the field which i included in the PERSONAL table. Hence the field [PEN] from PERSONAL table has to be included in your query, so that i can get a report of a particular employee..kindly ..........I tried like this but not working
    Attached Thumbnails Attached Thumbnails AJAX22.jpg   AJAX23.jpg  

  10. #25
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    in a new query, link this query to personnel table on PID - that will give you the PEN value

  11. #26
    rohinikrishna1987 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2020
    Posts
    36
    yes, it working, you are great man,.....

  12. #27
    rohinikrishna1987 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2020
    Posts
    36
    disturbing once more....for data entry for the ACR datas, I made main and sub form...main form consist of records in PERSONAL table and sub form consist of those in ACR table.
    All records in main form are already entered which will shown while opening the forms. But Records in ACR has to be entered. When I open this form all records will shown one by one. That is not required. I want to enter ACR details or edit for a particular employee. So I need to display the specific record in the main table, that can be used PEN. But even after many trial and run methord I couldnot find the exact way...How can I get it while entering PEN of a specific employee for entering ACR datas.. I am attaching herewith the forms i used....I have made a query by combine your query and PERSONAL table with parameter [enter pen]. But this query could not used for this forms as ACR form could not be updated...How much time you take to prepare for that brilliant 'query' which send me last week...just for curiosity...Thanks
    Attached Thumbnails Attached Thumbnails aja33.png  

  13. #28
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I don't have a lot of time right now. You have changed the requirement (or at least your explanation of what you require) several times. The query is not updateable - you talked abut needing a report - which by definition are not updateable.

    Best I can suggest is to base the form on the acr table and use dlookups in your form to look for missing periods to populate the missing data.

  14. #29
    rohinikrishna1987 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2020
    Posts
    36
    OK sir, no problem....I have found solution for that....I made a simple query for PERSONAL and simple query for ACR, then they are connected with main and sub form...In PERSONAL query put criteria that [enter pen]. Then it was ready to enter data for a particular employee who carry a certain PEN. It works super...Sorry for disturbing you....and changing my requirements several times....That happens only because my less knowledge in ms...Any way I have almost completed my project....And it worked excellent...I am always thankful for your great and kind contributions......Thanks a lot....If u dont mind i may contact u for another doubt in future for another project....Thanking u... Please keep in touch...Sorry for all the disturbances

  15. #30
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    If u dont mind i may contact u for another doubt in future for another project
    I don't always have the time, better just to start a new thread - and make sure you are clear about your final objective

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