Please reply to me
Please reply to me
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;
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
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.
looks like I included an extra D in ACRENDT
Thank u sir............................................... ...with love, its working.....thank u very much and may keep in touch.....thank u once again
happy to help
you are great
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
in a new query, link this query to personnel table on PID - that will give you the PEN value
yes, it working, you are great man,.....
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
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.
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
I don't always have the time, better just to start a new thread - and make sure you are clear about your final objectiveIf u dont mind i may contact u for another doubt in future for another project