Update/Append complex query

[tractorvix]

Hi Y'all.

I'm not sure if this is possible, or if I'm overcomplicating something that should be much simpler.

I have a table which contains date and hours information. What I want to do is if the number of hours is > 35 I need it to add the first record with the orginal date and then a second record with date + 7 and hours -35. Then take the second hours field and if this is > 35 add a further record with (date + 7) + 7 and (hours-35)-35 etc

so if I have a record that is job number 1
date = 20/03/2006
hours = 150

I need it to create a record for:
jobNo Date hours
1 20/03/2006 35
1 27/03/2006 35
1 03/04/2006 35
1 10/04/2006 35
1 17/04/2006 10

I'm sure there must be a way to do this using code, but I just can't seem to get my head round it.

Any help / advice is much appreciated.

Vix

 

[PHV]

And what have you tried so far ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tractorvix]

well,

I've been thinking it through and I've added an extra field which calculates the number of weeks (noofweeks) that need to be added.

I figure from this I can set up function to loop for X = 1 to noofweeks
then use maybe the addrecord to add newdate = Date + (7xX) and newhours = hours - (35xX)
loop through until X = number of weeks, then move on to the next record.

Not used the addRecord before, so wasn't entirely sure how to set it up.

Vix

 

[PHV]

Tip: Have a look to the Mod operator:
? 150 Mod 35
10

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886