I have a very confusing problem. I will try to explain it as clearly as possible. I have a WAD Master (Work Authorization Document) table which tracks the duration and cost of a project. The projects can have up to four phases and by contract there are a certain number of days allowed for each phase based upon project cost. I have a Cost Category which contains this info. When a new WAD is entered the cost cat is chosen and the four phases estimate start and finish dates are calculated from this table lookup. This works perfect.
Now it starts to get difficult. All work done on the WAD has to be charged against a Work Order. These work orders are issused for each phase. There can be multiple work orders per phase but only one work order per phase can be active at any time. There can be multiple phase work orders open at the same time. The Work Order table is related to the WAD Master by WAD Number. This all works great.
The real problem comes because you can have revisions against a Work Order. The Revision table is linked to the Work Order table via Work Order Number. This works fine but the hangup is the way the revision information is given. What is given as revision is a new WAD Finish Date and not a Phase Finish Date. In the Cost Category table I also have a field which says what the phase percent is for the number of days allowed for that cost category. So what I am trying to do when I receive a WO revision is first calculate the number of total days that the revision has approved for the WAD (they can be positive or negative) and then adjust the WAD Finish Date and then backing up from the new WAD Finish Date adjust the Phase Start and Finish Dates. In theory this is not have to do but because this is a Main Form (Wad Master), SubForm (Work Order) and a SubSubForm (Revision) I am having logic and syntac problems.
I have to keep a complete History of all WAD and Work Order dates for later analysis. I am wondering if I have my tables designed wrong? The user likes the way the Form, SubForm, SubSubForm screen looks but I am having major problems getting the logic to work correctly.
Any ideas or help would be greatly appreciated.
Thanks
Now it starts to get difficult. All work done on the WAD has to be charged against a Work Order. These work orders are issused for each phase. There can be multiple work orders per phase but only one work order per phase can be active at any time. There can be multiple phase work orders open at the same time. The Work Order table is related to the WAD Master by WAD Number. This all works great.
The real problem comes because you can have revisions against a Work Order. The Revision table is linked to the Work Order table via Work Order Number. This works fine but the hangup is the way the revision information is given. What is given as revision is a new WAD Finish Date and not a Phase Finish Date. In the Cost Category table I also have a field which says what the phase percent is for the number of days allowed for that cost category. So what I am trying to do when I receive a WO revision is first calculate the number of total days that the revision has approved for the WAD (they can be positive or negative) and then adjust the WAD Finish Date and then backing up from the new WAD Finish Date adjust the Phase Start and Finish Dates. In theory this is not have to do but because this is a Main Form (Wad Master), SubForm (Work Order) and a SubSubForm (Revision) I am having logic and syntac problems.
I have to keep a complete History of all WAD and Work Order dates for later analysis. I am wondering if I have my tables designed wrong? The user likes the way the Form, SubForm, SubSubForm screen looks but I am having major problems getting the logic to work correctly.
Any ideas or help would be greatly appreciated.
Thanks
