Access: Convert Date Range to corresponding months

[ogdcl]

Dear Friends......I have the data like:-

Emp_ID Start_Date End_Date Pay_Due Pay_Drawn
===== ======== ======= ======= ========
101 07-APR-2008 31-JAN-2009 15000 12000
101 01-FEB-2009 20-MAR-2009 20000 17000
102 01-FEB-2008 15-APR-2009 10000 90000

Each employee can have multiple lines in the table. We have to calculate each employees salary difference (from pay_due to pay_drawn) between startate and

End_date calculation is based on the number of days in corresponding month and the differnece of pay_due and pay_drwan.

for example data is:-

Emp_ID Start_Date End_Date Pay_Due Pay_Drawn
===== ======== ======= ======= ========
101 07-APR-2008 31-JAN-2009 15000 12000
101 01-FEB-2009 20-MAR-2009 20000 17000

an employee can have multiple lines:
each line should be treated separately:

Note: salary is calculated on [differnece of pay_due and pay_drawb] no. of days in each month based on the start and end dates)

solution is required by the query if possible otherwise any procedure be acceptable.

Solution is required:-
1) select employees first row/line criteria is based on its Start and End data and then data is splited from start_date to end_date by month title
and Pay_due and Pay_Drawn is calculted according to the days in a month and days are based on Start and End Dates.

like---
Start_Date:07-APR-2008
End_Date: 31-JAN-2009
In above case data is formatted from emp_id = 101
Calculates no. days in each month

OUTPUT SHOULD BE LIKE THiS: (periods expand from start to end dates)
---------------------------
Month Name: April May-08 June-08 July-08 Aug-08 Sep-08 Oct-08 Nov-08 Dec-08 Jan-09
No. of days: 24 31 30 31 31 30 31 30 31 31


Then caluclate the Pay_Due and Pay_Drawn according to the days in a particular month.

Thanx

Experts Please help out.....

[NeoPa]

Calculating the number of days and the difference between [Pay_Due] & [Pay_Drawn] is relatively straightforward. For the expanding columns situation you would need two things :

1. Data from somewhere that represents all the items you need to show.
   IE. You need some form of record source that includes all the months you intend to cover.
2. To expand the columns you'd need a CrossTab type query. Otherwise columns in SQL must be predefined in the SQL itself.

I don't understand why, in such a scenario, the individual columns would need to be separated, but if they must be, then this is where you would need to start and get sorted out before going any further. To be honest, I have little to no experience with CrossTab queries, but I may be able to help with the basic data if you have further questions on it.

[ogdcl]

Thanks dear for ur timely response....

Ur righ, Calculating the number of days and the difference between [Pay_Due] & [Pay_Drawn] is relatively straightforward.

i want to present the data scanario first then the what is required from the data and lastly the solution is required (in the form of any query or procedure etc).

Data in the table is stored like this:

Emp_ID Start_Date End_Date Pay_Due Pay_Drawn
===== ======== ======= ======= ========
101 07-APR-2008 31-JAN-2009 15000 12000
101 01-FEB-2009 20-MAR-2009 20000 17000
102 01-FEB-2008 15-APR-2009 10000 90000

an employee can have multiple records:
in the above data shows the start and end periods in which employee is paid by pay_drawn and pay_due shows the due amount.
The requirement is:-
Now we have to organize the data in a new table by months column names and each month is based on the number of days in start_date and end_date and months also be calculated from start_date to end_date and finally the difference of pay_due and pay_drawn be calculated in each month..
e.g:
101 07-APR-2008 31-JAN-2009 15000 12000
months be calculated sepratly from april-08 to jan-09 and based on number of days present in the start and end dates. and each month have data (difference of pay_due and pay_drawn calculated on number of days in extracted months)..

present the basic data and how to extract the required information from the basic data.

I hope that i may try to give u the whole scanario for better understanding the problem......


Shah....

[NeoPa]

Shah,

I'm not about to do it all for you. I thought I'd already pointed you in the direction you need to take. Any further questions or request for help would need to be framed within that context, or if that's not possible for some reason, then the reason needs to be explained. Personally, I'm pretty confident that what I've said is right, so over to you to make some progress.