Date field-manage global change of all

B

Bonnie43

Hello: I have a field named proposedcompletiondate. I am requesting help with
this: If I change the date for one record, then i would like all the other
records with a date in this field to be changed automatically. In other
words, if the completion date is changed from 5/24 to 5/26, then all the
other records proposedcompletiondate will be changed , adding two more days.
I am a newbie when it comes to this,so please explain in simple-ease.

thanks for all your help...
 
R

Rick B

This does not make much sense. If you have five different projects that all
happen to be due on the same date, and you change the due date for one of
these projects, why would you want the others to change as well? If they
are all truly tied together, then you should not have the proposed
completion date multiple times in your tables, you should have a related
table and only have it once.

For example, let's say you have a PROJECT that involves five different
TASKS. These will all be do on the same date. You get permission to change
the due date, so all five tasks will change. The problem here is that the
TASKS should not have the due date, the PROJECT should. This is a basic
"normalixed design" issue.

The only way to do what you want if you do have the field in more than one
record would be to run an update query where you'd find all the records with
date of XX/XX and update it to XX/XX +2.

From what little you posted, I would expect your design is flawed.
 
B

Bonnie43

I hope I can explain myself here.... I created a database for our maintenance
department. It is just as you explained, all the records include the field
projectedenddate, and each record is considered its own project. The tasks
are in a different table and are not date sensative. However, If a project
date gets changed, then this will affect all the other open projects and
those dates need to change to reflect the new date. I don't think
normalizing is necessary in this case. I am a little worried about the update
table, only because what if it is 3 days and not two days that these get
pushed out to?? This is so hard to explain and take care of on-line. But
thanks for your answer anyway.
 
R

Rick B

I still think your structure is wrong. If all these items are linked to one
due date, then that due date should be stored one time in a master record.

You could have a main form with your 'overall' data such as due date, and
then a subform with the related tasks that have the unique details. The
user could then make the change on the main form.


If you insist on keeping it the way you have it (which in my opinion is just
a glorified spreadsheet) then you would want to run an update query. You
would specify when you build it or run it, what date you wanted to change
to. This could be 2 days away, 3, whatever; you'd tell it when you run it.
 
R

Rick B

To further that first paragraph, any field that is the same in every record
would go up in your 'main' record. So, If you have 10 tasks that are all
assigned to the same person and all due on the same date, your userid and
duedate would be in the main record, not the detail record.
 
Top