Updating dates/times according to specified incriments

[airwitte]

Hi,



I have an Access database that I am building to help depts track customer orders. They put in the following info:

1.)start date, 2.)start time, and an estimated 3.)end date & 4.)end time.

I want to have the following flexibility: if any of the 4 fields is changed in any way, I want the appropriate preceding or ensuing records to move forward/backward in time based on how I altered my record of choice.

Here's an example
Record one: start date- 12/5/10, start time- 3:00 pm, end date-12/6/10, end time-11:00 am.
Record two: start date-12/6/10, start time- 11:30 am, end date-12/7/10, end time- 10:00 am.

If I move the Start Time from record 2 up by an hour (12:30), then I would like everything in record 1 to move up by 1 hour (start time of 4pm and end time of 12:00 the next day). Basically, I want to specify some incriment of time (probably hours) as a requested input and have everything adjust for that.

What makes this more complex is that I have to follow our warehouse work schedule, which means that if we shut down at 4pm daily, and I move a finish time from 3:00 up 2 hours, I can't have it say the job finishes at 5pm. I have to have it show that is finishes an hour into our next production day.

I'm thinking of doing some kind of append, update, or transform query, but am not sure. What would be best to accomplish this?

[NTC]

its complicated; not sure a forum can answer this for you.

you need to divide the solution into steps;
add values
flag if >4:00
calc amount beyond 4:00
identify records after flagged record
add calc amount to identified records

it will involve a whole series of queries, both plain old select queries (for calculated fields and to find correct records) as well as update queries when you are ready to update fields.

you gotta think each step thru and build the query(s) that make it work. then step thru each manually considering every combination of changes the user might have.

then finally you stack them all together in a macro

hope this helps you at least get going in the right direction.

[William McKinley]

Yea, I gave this a shot last night and failed miserably. I might give it another shot today if I get some free time.