Dates

[wh00t]

Hi

I have a table that stores start date, end date and hours

is there any way that I can split the hours for the dates

i.e. start date 01/01/2002, end date 01/04/02, hours 30
want to split so result would be Jan - 10.67, Feb - 8.71, Mar - 10.67.

I have tried a crosstab query but this just puts all 30 hours in Jan.

 

[cogent1]

Please explain how you enter the hours. Does each entry for hours have an associated date? If so, you can use the Datepart Function in your crosstab to give you column headings (DatePart("m",[Hours])and use the sum of hours as the value.

 

[wh00t]

the table that has this information has the following fields

Name - DateStart - DateFin - Hours


the information that I typed earlier is one record, is there any way using a query that I can divide the hours entered by each month that is in the range between start and finish date?

 

[cogent1]

Let's take your example, then. You have 90 days =30 hours. so each ratio is

Jan 31/90*30=10.33
Feb 28/90*30 =9.33
Mar 31/90*30=10.33

These don't yield the results you gave. (In fact, your sums total 30.05. ) How do you apportion the hours?

 

[wh00t]

I didn't take the time to work out what the results should have been, this is the mail that was sent with the database to me

How can the 30 hours be split accurately over the 3 months to end up with 3 lines for Name Ed instead of 1 line for Ed


Name Date start Date Fin Hours
Ed 01/01/2002 01/04/2002 30


to go to


Name Date start Date Fin Hours
Ed 01/01/2002 31/01/2002 10.67777777777
Ed 01/02/2002 28/02/2002 8.71111111111
Ed 01/03/2002 31/03/2002 10.67777777777

 

[cogent1]

Having given the matter some thought, I don't see a way in which this can easily be incorporated into a straight query, because a way has to be found to define the intervening months beween the dates and then count the number of days in each month.

A function can handle it well enough though.

Are the periods always whole months?