I am developing a scheduler. Its all pretty complicated and I have a LOT of tables related in various ways. I have normalised the database quite well I think. However I have come up with a knotty problem.

I have a table [Project] which can have one or many [People] these people have to do a set of [Tasks] (a person can have many tasks but a task can have only one person) which have a pre-defined schedule. Each time I create a project and assign a person to that project I need to create five tasks which are done in a pre-defined timespan - a regular schedule - however, things sometimes go wrong and additional tasks sometimes have to be added. All no problem. However the last task is always the same (shipping) and has to be completed four weeks after the date of the penultimate task so I need a way to automatically adjust the final task date (the shipping date) to four weeks after the penultimate task. It is possible that things go horribly wrong and two or three more tasks have to be added, but the final task still needs to be done four weeks after whatever the last but one task is.

Of course it is more complicated than that as in fact the last task is in fact a chunk of three or four tasks that follow on at predefined intervals immediately before the above-mentioned "final" task date.

any ideas on how to solve this?

Many thanks,

Alan.

An additional note:

Ideally I would also like to display the tasks in a portal, so it may be possible to display the sorted-by-date list of tasks in a portal (easy) and somehow get the (date) value of the last record in the portal and then have a few calculation fields (the tasks that never change) which are based on the date of whatever the last date in the portal is but I just don't know how to do this or even if it is possible,

thanks

Alan.

Edited November 7, 200520 yr by Guest

OK I managed to find out myself so here is a solution:

Just use the Max function in a new calcultaion field and return a date value of the related record you want to find and it will find the highest value in a portal of related records. Then you can base further calcultations on this....coool! B)