Calculating multiple date fields based on value of preceding field

[archie1_za]

Hi



I hope someone can help me with the following:

1. I have an invoice date that is stored in an invoice table:

2. There are 5 different tasks that need to be completed. Each Task has 3 date fields - In a TASK table

Task1 has a :

• Basedate that is determined by the invoice date + 5 Days (Locked - User cannot change)
• PlannedCompletionDate = BaseDate +1 (Locked - User cannot change)
• ActualCompletionDate = ( User Input)

Task2 has a :

• Basedate that is determined by the Task1:BaseDate + 1 Days (Locked - User cannot change)
• PlannedCompletionDate = Task1:ActualCompletionDate +1 (Locked - User cannot change)
• ActualCompletionDate = ( User Input)

Task3 has a :

• Basedate that is determined by the Task2:BaseDate + 1 Days (Locked - User cannot change)
• PlannedCompletionDate = Task2:ActualCompletionDate +1 (Locked - User cannot change)
• ActualCompletionDate = ( User Input)

.... and so on.. for all 5 tasks

For Each Task as well I need to have the following status' in a status field - please ignore syntax...

IF Planned <= BaseDate THEN " In Progress"
IF Planned > Basedate THEN "Delayed"
IF Actual <> " Completed"

Also a number of days delayed field ..lets call it Delayed
= difference between BaseDate & Actual Date. (This will either return a -'ve number if its within time or a +'ve number if its delayed by how many days.


All that the user needs to capture via a form is the actual completion date per task which is the only field i will have in the table??
The rest of the fields can be created on the fly via a query. I understand that in the "Criteria" field in a query its not advisable to use a "created" field name .....

I think I would be able to this with a query ontop of a query ontop of a query...etc... but that just seems cumbersome

to give you an idea ... there are 40 units that each have 5 tasks which in turn have 3 dates each!!!!

Is there an easier way? Any advice would be welcome.

Thanking you in advance

[ranman256]

a record cannot see the records around it.
you can see all fields of the current record but not the ones ahead or behind.

you would need code to scan the entire recordset,
save the current record values,
move to the next record then perform a function.

[orange]

I recommend you build a data model based on your business description. We do not know the details of your business. But it appears you have 5 Task types, each with specific attributes. You mention 40 units but we have no idea what a unit is.
Also, you might consider using Projects for your work that involves Tasks. If Project contains 4 tasks, a Task could be complete , yet the Project still incomplete.

Readers need more details to offer more focused advice.

You should have a clear set of requirements that you understand before starting to build your physical database.

Good luck.

[archie1_za]

@Orange - Thank You for the reply

Currently in an Excel Spreadsheet it is listed as follows:

|Invoice Date | Task 1 |Task 2 |Task 3 |Task 4 |Task 5 |

These 5 tasks make up one section of a workflow - a unit - but we can refer to it as one table.

Each Task field is a date field and currently only captures the completed date.

What is required is the following:

Survey Table
|Invoice Date |
| Task 1 Planned Start = Invoice date +5|Task 1 Planned Complete = Planned Start + 1 |Task 1 Actual Complete |Task 1 Status |Task 1 Days Delayed |
| Task 2 Planned Start = Task 1 Planned Start +1 |Task 2 Planned Complete = Task 1 Actual Complete +1 |Task 2 Actual Complete |Task 2 Status |Task 2 Days Delayed |

...and so on until task 5


Then The next Table/Unit/Section will start - i.e. Implementation Table - which has 5 tasks

...and so on...
So the Survey Table will have only 5 static fields (actual completion date) and 20 calculated fields (from the query) fields if I calculate correctly.

I have managed to complete the first part in a query (Survey Table) and got it to work as required. I wanted to inquire if there may be a simpler way of doing it as I will have to create 40 of the tables.

I do apologise if I'm not explaining clearly but I am a complete novice in ACCESS.

Thanking You Kindly.

[NoellaG]

Hi,

first thing to do is to let go of the Excel logic. Before you start a database design, perhaps take some time to study normalization (a lot of info there on the internet). As I look on it you only need 2 tables: tblSurvey and tblTasks.
tblSurvey with fields like : ID (autonumber), InvoiceNumber (text or number), InvoiceDate (date)
tblTasks with fields like : ID (autonumber), SurveyID (long integer), TaskNumber (integer) ActualStartDate (date), DateCompleted (date)

linked on tblSurvey.ID = tblTask.SurveyID

In your example the planned startdate for a task can be calculated as tblSurvey .Invoicedate + 5 + tbltasks.Tasknumber.

Kind regards
Noëlla