Indicate when a product in table has not timely moved to next phase 1

[Sherman6789]

Hello,
Our department produces and prints products on a monthly basis. There are about six phases that each product goes through before it is either completed or canceled. For discussion purposes I'll call them Phase1 - Phase6. In reality, they are referred to by name such as "in edit, in review, in reproductions, etc." Not all items need to go though all of the six phases of completion and they may have various scheduled arrival dates for each phase. A production can be stopped at any time and cancelled or completed early.

I would like to have a form that shows the order of the Phases with space for a corresponding Date when the new phase is reached. I would also like space for a "tickler date" for each phase. If a product has not reached the next date by the tickler date it should turn red or blink or at least show up on a report of products which are late reaching their next phases so the managers can learn why. If a product is cancelled or is completed before the scheduled arrival for the next phase; phase checking should be ignored.

My guess is that the table should be written similar to the following. Please correct me if I am wrong:

ItemName
Department
Phase1
Phase1-date
Phase1-Tdate
Phase2
Phase2-date
Phase2-Tdate
Phase3
Phase3-date
Phase3-Tdate
Phase4
Phase4-date
Phase4-Tdate
Phase5
Phase5-date
Phase5-Tdate
Phase6
Phase6-date
Phase6-Tdate
Completion-date
Cancelled-date

I also guess that the program will check the dates from from bottom-up and compare each date against the current date. It seems to me that the program will automatically ignore or skip any Phase date with no entry. This is the way that it will know that that a particular phase is not applicable to the current Item.

That's is about as far and I know to go. Please help.

 

[lameid]

Personally, I would put the phases in its own table so you would have one record for each phase rather than columns.

Then I would make a query to determine which is the next phase... The minimum phase (probably a sequence field in a table that lists phases) that has not been complete. Then it is a simple matter to report if its due date is past, or upcoming.

The tables I am thinking of would be something like:

Phase Table
PhaseID Autonumber
Sequence Long Integer
PhaseName Text (20)

Phase Schedule
ItemID
PhaseId
PhaseDueDate
PhaseCompleted

I am assuming that all process are completed in the same order of phases otherwise you will need another set of tables to address that.

I hope that all makes sense.

 

[Sherman6789]

Thanks lameid for your comments.

The main form shows all types of information about each item. There are also tabs on the form which indicate various costs, assigned editors, authorized users, etc. One of the tabs will list the schedule of phases. This will show a history of progression from one stage to the next. Next to each date is a tickler date. If the item has not shown that it has progressed to the next phase by that date, the item will be flaged in some way for investigation.

Will placing the phase names and dates into a separate table make this any easier? All of the dates for each phase will remain visiable any time the tab is opened for the item or items. A query will highlight the last phase for comparison; however, all phases need to be continously shown with their dates.

WRS

 

[lameid]

Phases in a separate table helps because you can control sequence at a global level.

Also using my recommendation, you would have to add all the phases to the schedule when you create an item or some other appropriate timing event. It is a bit more work in the begining but far simpler in the long run when you have to do analysis and reporting.

 

[Sherman6789]

Hi Lameid,

Am I correct, to show all of the phases and dates of a product using your phase table that I will need to use a subform?

 

[lameid]

Yes, you should use a continuous subform assuming you want to update information. If you do not want to update it on a screen you might consider a listbox but most likely the subform is better all the way around.