task schedule

[Brigitte P]

I have a database with equipment that has certain task schedules, e.g.,
weekly, monthly, bi-monthly, quarterly, semi-annual all the way to every five
years. Our accreditation folks require that we show when the tasks where
completed as required and when not, something like:
Equipment Schedule Scheduled Date Completed Date
Missed
Air handler Weekly 6/1/07
Y
6/8/08
6/7/07 N
6/15/07
Y
6/22/07
6/20/07 N

How can I generate the list of the due dates that are independent of the
completion dates. The task is due whether it was completed on the previously
required date or not. I probably also need to accomodate a range for
completion, probalby +/- 2 days or so. I think I have seen something like
that someplace, but I don't remember where.
Thanks for any input.
Brigitte P.

 

[Steve]

Use a query with a calculated field for Missed:
Missed:IIF([CompletionDate] Is Null,"Task Not
Completed",IIF([CompletionDate] Between ([ScheduledDate]-2) And
([ScheduledDate]+2),"Y","N"))

Watch the word wrap!!

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Brigitte P]

Thank you. Yes I understand how to do this part or at least could have
figured it out. My problem is to generate the task schedule dates, that is,
from a start date, generate a continous task schedule date adding 7 (or
whatever) days automatically. The workorders need to be issued regardless
whether the previous schedule was done or not so I can't use the completion
date as a starting point. It needs to add the days to the original task start
date and continue on until the equipment is disposed. We probably should be
able to pull up what was not done Between Date 1 and Date 2 which I know how
to do via an unbound form.
And additional help is greatly appreciated.
Brigitte P.

Use a query with a calculated field for Missed:
Missed:IIF([CompletionDate] Is Null,"Task Not
Completed",IIF([CompletionDate] Between ([ScheduledDate]-2) And
([ScheduledDate]+2),"Y","N"))

Watch the word wrap!!

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

I have a database with equipment that has certain task schedules, e.g.,
weekly, monthly, bi-monthly, quarterly, semi-annual all the way to every
five
years. Our accreditation folks require that we show when the tasks where
completed as required and when not, something like:
Equipment Schedule Scheduled Date Completed Date
Missed
Air handler Weekly 6/1/07
Y
6/8/08
6/7/07 N
6/15/07
Y
6/22/07
6/20/07 N

How can I generate the list of the due dates that are independent of the
completion dates. The task is due whether it was completed on the
previously
required date or not. I probably also need to accomodate a range for
completion, probalby +/- 2 days or so. I think I have seen something like
that someplace, but I don't remember where.
Thanks for any input.
Brigitte P.

 

[Steve]

I would start with the following tables:
TblTaskScheduleSequence
TaskScheduleSequenceID
TaskScheduleSequence
DaysInTaskScheduleSequence

TblEquipment
EquipmentID
EquipmentName

TblEquipmentTask
EquipmentTaskID
EquipmentID
EquipmentTask
TaskScheduleSequenceID

TblEquipmentTaskSchedule
EquipmentTaskScheduleID
EquipmentTaskID
ScheduledDate
CompletedDate

Note: Missed is a calculated value.

2. Create a form and fill in TblTaskScheduleSequence (Weekly, BiWeekly,
Monthly, etc)
3. Create a form and fill in TblEquipment
4. Create a form and fill in TblEquipmentTask
5. Create a query that includes all of the above tables and includes the
fields:
EquipmentName from TblEquipment
EquipmentTask from TblEquipmentTask
DaysInTaskScheduleSequence From TblTaskScheduleSequence
ScheduledDate From TblEquipmentTaskSchedule
EquipmentTaskID From TblEquipmentTask
6. Write code that iterates through all EquipmentTaskID in the query in 5,
and at each EquipmentTaskID start Schedule Date to TblEquipmentTaskSchedule
then adds DaysInTaskScheduleSequence to the previous Schedule Date, checks
if the new date is less than or equal to the end Scxhedule Date and if it is
adds EquipmentTaskID and the new Schedule Date to TblEquipmentTaskSchedule.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

Thank you. Yes I understand how to do this part or at least could have
figured it out. My problem is to generate the task schedule dates, that
is,
from a start date, generate a continous task schedule date adding 7 (or
whatever) days automatically. The workorders need to be issued regardless
whether the previous schedule was done or not so I can't use the
completion
date as a starting point. It needs to add the days to the original task
start
date and continue on until the equipment is disposed. We probably should
be
able to pull up what was not done Between Date 1 and Date 2 which I know
how
to do via an unbound form.
And additional help is greatly appreciated.
Brigitte P.

Use a query with a calculated field for Missed:
Missed:IIF([CompletionDate] Is Null,"Task Not
Completed",IIF([CompletionDate] Between ([ScheduledDate]-2) And
([ScheduledDate]+2),"Y","N"))

Watch the word wrap!!

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

I have a database with equipment that has certain task schedules, e.g.,
weekly, monthly, bi-monthly, quarterly, semi-annual all the way to
every
five
years. Our accreditation folks require that we show when the tasks
where
completed as required and when not, something like:
Equipment Schedule Scheduled Date Completed
Date
Missed
Air handler Weekly 6/1/07
Y
6/8/08
6/7/07 N
6/15/07
Y
6/22/07
6/20/07 N

How can I generate the list of the due dates that are independent of
the
completion dates. The task is due whether it was completed on the
previously
required date or not. I probably also need to accomodate a range for
completion, probalby +/- 2 days or so. I think I have seen something
like
that someplace, but I don't remember where.
Thanks for any input.
Brigitte P.

 

[Brigitte P]

I'm not a programes, just a lay person tasked with doing Access databases. So
I may need some help with writing code that "iterates through all
EquipmentTaskID." However, my facility has put the project on hold trying to
maybe revive the stoneold SQL dbase that is doing these tasks right now. I
just never thought of creating a table with Task Schedule, and also looking
at the possible completion date and not changing the start date if the task
was not completed. I think this is the key, and I may be able to take it from
there if we re-start the project. I've printed your replies as a reference.
This was really helpful. If I need more help in the future, I'll re-post.
Thank your for helping me to think this through.
Brigitte P.

I would start with the following tables:
TblTaskScheduleSequence
TaskScheduleSequenceID
TaskScheduleSequence
DaysInTaskScheduleSequence

TblEquipment
EquipmentID
EquipmentName

TblEquipmentTask
EquipmentTaskID
EquipmentID
EquipmentTask
TaskScheduleSequenceID

TblEquipmentTaskSchedule
EquipmentTaskScheduleID
EquipmentTaskID
ScheduledDate
CompletedDate

Note: Missed is a calculated value.

2. Create a form and fill in TblTaskScheduleSequence (Weekly, BiWeekly,
Monthly, etc)
3. Create a form and fill in TblEquipment
4. Create a form and fill in TblEquipmentTask
5. Create a query that includes all of the above tables and includes the
fields:
EquipmentName from TblEquipment
EquipmentTask from TblEquipmentTask
DaysInTaskScheduleSequence From TblTaskScheduleSequence
ScheduledDate From TblEquipmentTaskSchedule
EquipmentTaskID From TblEquipmentTask
6. Write code that iterates through all EquipmentTaskID in the query in 5,
and at each EquipmentTaskID start Schedule Date to TblEquipmentTaskSchedule
then adds DaysInTaskScheduleSequence to the previous Schedule Date, checks
if the new date is less than or equal to the end Scxhedule Date and if it is
adds EquipmentTaskID and the new Schedule Date to TblEquipmentTaskSchedule.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

Thank you. Yes I understand how to do this part or at least could have
figured it out. My problem is to generate the task schedule dates, that
is,
from a start date, generate a continous task schedule date adding 7 (or
whatever) days automatically. The workorders need to be issued regardless
whether the previous schedule was done or not so I can't use the
completion
date as a starting point. It needs to add the days to the original task
start
date and continue on until the equipment is disposed. We probably should
be
able to pull up what was not done Between Date 1 and Date 2 which I know
how
to do via an unbound form.
And additional help is greatly appreciated.
Brigitte P.

Use a query with a calculated field for Missed:
Missed:IIF([CompletionDate] Is Null,"Task Not
Completed",IIF([CompletionDate] Between ([ScheduledDate]-2) And
([ScheduledDate]+2),"Y","N"))

Watch the word wrap!!

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)



I have a database with equipment that has certain task schedules, e.g.,
weekly, monthly, bi-monthly, quarterly, semi-annual all the way to
every
five
years. Our accreditation folks require that we show when the tasks
where
completed as required and when not, something like:
Equipment Schedule Scheduled Date Completed
Date
Missed
Air handler Weekly 6/1/07
Y
6/8/08
6/7/07 N
6/15/07
Y
6/22/07
6/20/07 N

How can I generate the list of the due dates that are independent of
the
completion dates. The task is due whether it was completed on the
previously
required date or not. I probably also need to accomodate a range for
completion, probalby +/- 2 days or so. I think I have seen something
like
that someplace, but I don't remember where.
Thanks for any input.
Brigitte P.