Maturity Date in Access

[mba_110]

Dear All,

I am doing calculation for employees for their leave and Air ticket balances, firstly I want you to have their basic records like entitlement, balance & utilization etc.

The basic things that can be count in this calculation is below.


Fields Name in VacationSchedule_Query
Emp_ID Text Field Employees_table
HireDate Date Field Employees_table
EmployeeName Text Field Employees_table
Accrued_Tkt Number Field Airticket_Query
Airticket_Utilized Number Field Airticket_Query
Airticket_Balance Number Field Airticket_Query
Accrued_Days Number Field Leave_Query
Leave_Utilized Number Field Leave_Query
Leave_Bal Number Field Leave_Query
CalculationDate Date Field Contracts_table
EmpStatus Text Field Contracts_table

I need results & Functions for below fields in VacationSchedule_query
LeaveEntitleBy:
AirticketEntitleBy:


Now I need some condition to produce the query criteria and I need result based on following conditions.

I need if [EmpStatus] is Terminated or Resigned than no need to calculate anything
I need if [EmpStatus] is Active than formula have to check the Maturity Date of his Yearly entitlement value which is leave and Air ticket as mentioned above, for example if [Emp_ID] AA is having 12.5 in his [Leave_Bal] and 0.82 in his [Airticket_Balance] than I need what date his Leave value reach to 23 and Airticket value reach to 1 in Above result fields I need that future date, same should be treated for negative numbers if any employee already in minus then need date to reach him in positive for 23 and 1 this code should work for this also.

Now one more thing need to take care that [CalculationDate] is not today’s date and balances coming from different dates as its stored in Contract_table and every employee has his different contract entitlements and its divided in two parts as followed.

[LeaveTill2011] - for Some employees contract are different till 2011 mean it will work for those who hired before 2011
[LeaveFrom2012] - for some employees this portion is reconditioned who join after 2011 as well as who continue from above.

[TktTill 2011] and [TktFrm2012] field are the same way of above leave to calculated.


I know its look like complicated I do try to mention everything, if incase I forget to tell anything then please let me know.


Many thanks in advance for your kind help.

MA

 

[Micron]

You've had several views but no suggestions. I wonder if it's because you're asking way too much all at once, and you are quite correct - it is complicated (way too complicated).
Perhaps if you broke this up into chunks by dealing with your next and/or most pressing problem so that you can make some progress and solve as you go. Otherwise, you might be looking at a contract job for somebody to work with you to get to the end result, because communicating over an issue as big as this would be a very long forum thread indeed.

 

[mba_110]

Thnx Miron

I managed to create query now i have fields ready at least with totals and numbers.

lets say i have [CurrentDate] values based on their contract terms as mentioned above, now i am looking for final touch i need the dates where these figures reached to 23 in leave and 1 in tickets field, i need those dates.

Can you help me with this? i know in excel its EDATE function but i dont know what should be use in access 2010 to calculate above.


regards,

MBA

 

[Micron]

i need the dates where these figures reached to 23 in leave and 1 in tickets field, i need those dates.

I'm guessing because I have no idea what you have to work with. If this is a query, put >=23 in the leave field criteria row and 1 in the tickets field. If this messes up your query, you could instead create another query that uses the first query as a table and add the criteria as noted. I have no idea where you're putting or viewing the results, so I think that's the best I can offer at this point.

You should always make suggested changes in copies of your db or table/query/form etc. until satisfied with the result.