Structure advice, how to ensure tasks completed before changing value of field

[RECrerar]

Firstly sorry for the slightly confusing title, was a bit stuck on how to summarise in one scentance.

Basically I was hoping someone could advise me on the best structure to use for part of the database.

The area working on is this.

There are several projects, each project can be in anyone of 9 statuses and each of these statuses has a series of tasks related to it that must be completed before the status of the project can be advanced.

I am assuming that the user would specify which tasks had been completed by the use of checkboxes or the like and then the database would check which tasks had been completed and if some tasks for a particular staus are not complete not allow the user to change the status up.

I'm really confused as to the best database structure. Currently I have:

Table: Status Details:
This is a very small table with two fields
Status_ID
Status

Table: Task Details
this table lists all the tasks and their related statuses
Task_ID
Status (Linked to Status_ID)
Task Description
Compulsary ((Yes/No) not sure yet if this field in necessary or if all will be compulsary but figured best to start off with the more complex scenario and then more to the simpler one if necessary rather than visa) versa

Table: Main Project Table
This will be the main table containing the project data. Will have a Status Field and possibly a "Next task to be completed" field among others.

And this is where I get stuck. I need somehow to link the Task_ID to the projects. I am thinking that I obviously need another table to do this. Would it be best to have this table have a field for project ID and then fields for each individual task each being a yes/no field and then use some code hidden behind whatever forms/queries I use to get the information in a presentable for to the user to determine what the highest status a project can have is. Another option is to change to format of the table I have and have a "Required Tasks" column in the status table that lists the task ID of all tasks required for that status and then use this as a starting point.

Sorry this is really long winded. I hope it makes some sence. Any inputs would be appreciate

 

[KenHigg]

There are a number of good ways to do this. My suggestion is to have the status/tasks tables set up as master type data and every time you create a new project the master data is duplicated over into another table(s) which is linked back to the project table. That way if you modify the task list you maintain the original data.

Hope that makes sense.

 

[RECrerar]

There are a number of good ways to do this. My suggestion is to have the status/tasks tables set up as master type data and every time you create a new project the master data is duplicated over into another table(s) which is linked back to the project table. That way if you modify the task list you maintain the original data.

That does sound the type of thing I need.

I do need to state that I am very new to Access at the moment. I kinda agreed to the project as I have reasonable experience with Excel and Excel VBA and I figured "so how hard can it be?" am now finding the answer is "pretty d*am hard!"

Anyway, so I have set up a table that has the following fields

Project
Status
Task A1 'basically a field for every task that needs to be perfomed
Task A2 'These are all yes/no check boxes
Task B1
Task B2
task B3
etc...

So the first question is, how do I ensure a new record is added to this table each time a new project is created? Would this require code or is there an Access cabaility that can do this?

Secondally how do I relate the completed tasks to the status? Would this be easier done with a series of very small tables each one containing the tasks for a certain status?

I have also started putting together a form for the user interface (few specific questions about this but they can wait for now). Generally I was thinking, that in order for the form not to be too cluttered it should only display the tasks for the project's current status rather than all possible tasks. I was looking at doing this by having a set number of checkboxes and then altering the fields they relate to using code. Do you know if this is a sensible way of doing this and would it tie in with the table format I have above or can you suggest a better method?

Hope those aren't too many questions. Thanks, Robyn

 

[KenHigg]

Give me a little while to work on this -

 

[RECrerar]

Thanks so much, I will keep on at it myself too but not sure I'll get anyway and time soon.

Just another quick thought:

So the first question is, how do I ensure a new record is added to this table each time a new project is created? Would this require code or is there an Access cabaility that can do this?

Was just thinking about this point. Is there any advantage in having them as seperate tables. I could just put the tasks as fields in the main project table that way there would laways be a set of tasks for each project. I was just previously thinking that splitting them up would make for a neater structure, but if there is no advantage then I don't need to.

 

[KenHigg]

Here's kind of where I was going:

(Hopefully you can take it from here )

 

[RECrerar]

Hey thanks for that. having just had a quick look at it, I very much like the design of the form so think I will be using something like that. I have managed to write a rather clunky but effective code to limit changes to the status when the task have not been completed (only when all tasks are visible in the form but it's a start for now) so I shall look at incorporating that with your format as a starting point.

Thnkas for taking the time to do that. will let you know how I get along

 

[KenHigg]

Cool - Glad to help.

 

[neileg]

If you have a robust set of rules for the status, don't allow the user to change it at all, calculate the status based on the task data.

 

[RECrerar]

If you have a robust set of rules for the status, don't allow the user to change it at all, calculate the status based on the task data.

I thought about that, the difficulty arrises in that projects will not necessarily be advanced to the next status as soon as all tasks are performed. I could add an extra task with the sole purpose of advancing the project but I'm not sure if that might confuse the managers using it. Also it's not always a straight sequence of statues, it is mainly but project can move backwards in status as well as forwards. For example the first three statuses are:

Prospect
Intended
In Progress.

So from starting as a prospect the project can only go to Intended, but from intended the project can either advance to In Progress or go back to just being a Prospect and likewise In Progress can advance to the next stage (submitted) or go back to either Prospect or Intended. So what I'm trying to say is that just because all the tasks for a status are completed it does not have to be in the next status, it just can't be in a further along status if the tasks have not been completed. Therefore I think the user does need control over the status.

KenHigg - I do have a couple of questions about the working of your database but I think I will take a bit mroe time to look at it before asking questions I may be able to answer myself, just to give you advance warning that there may be some more questions.

 

[RECrerar]

Okay, so I have attached two database projects. One "Structure (again)" or something like that is based on your design, it cureenetly doesn't work fully as it only has one set of tasks so all the projects have the same tasks completed, which is obviously not satisfactory but I am assuming that this is where your tblProjectTasks table comes in. I assume this would need records for each task in each project. Again is it possible to automatically add these each time a new project is added? I also haven't done anything on the validation side of it yet.

The second one "Updated structure" was what I was working on before the change of tact to use yoru structure. It has he validation in place but as you can see has all the tasks listed for each project, which would not look good on the form. I had put together a form that had a set number of checkboxes and the tasks these related to changed when you changed the status but not sure what I've done with that.

So couple of questions:

The validation I had set up on "Updated Structure" uses code to check that certain fields in each record are checked, using your method I would need to check that a specific field in several records was checked, is this possible to do through the form? If so do you know which commands I should be looking at using?

generally any comments on either structure would be appreciated.

Thanks again, Robyn

 

[RECrerar]

Okay so this is what I have now, and it seems to work,

So unless anyone can see any obvious flaws that could be a problem later withthis method I think I will stick with it as this was I can add the tasks as fields in the main project table simplifying the need to have tasks for each project. The form doesn't look quite as nice as Ken's one, I like the whole subform method but I think the code to check that the tasks are completed is easier to write based on fields rather than records.

Thanks Ken so much for your help.

 

[KenHigg]

Just poking around with your db and came across the fact that you may want to consider what happens if a user needs to back up. Say if the project is marked complete and you need to back up by one status - what happens then?

 

[RECrerar]

Erm, Currently they just change the status back one, nothing happens to the tasks.

I'm not too sure how that will need to work in reality yet, like whether it should reset all tasks later than the current status if the status is lowered or if they can still be classed as done. I am assuming as most of them will be "complete such and such document", "submit cost plan" and so forth that they will have to be redone and hence I should build in some sort of capability to do this but I don't really know yet and the boss is on holiday till Monday so I don't think I'll really be able to answer that until he gets back and I can get more information on exactly what capabilities are needed

 

[RECrerar]

Also I have no idea if we will need proof that tasks have been done, for example checking for hyperlinks to documents and so forth. I sense this could get quite complex, but hey it'll be a learning experience. The test will be to be able to get something with the desired capabilities that isn't so clogged up that it still runs at a reasonable spead

 

[KenHigg]

Maybe I missed something - When I unchecked the last task and attempted to change the status back one it wouldn't let me. I'll try again...

 

[RECrerar]

Oh really?

I will have a look at that. It's probably due to how I've related the tasks to the statues (a miss-match between the code and the the status to which the tasks actually belong).

You should be able to go back a level if you uncheck a task

 

[RECrerar]

Oh I know why that is. It's becasue I just changed the caption of the task that appears in "Complete" and on it's control source so you culd be unchecking an earlier task and then the code (rightly in it's mind) thinks that you have not completed all the required tasks. The code will not have that quick asthetic in the actual database so shouldn't be a problem. Sorry for any confusion

 

[KenHigg]

IMHO - That's some pretty cool stuff you're doing. Once you get the thing working the next thing to do is to figure out what you would need to do to make in portable so that you can use it in other applications with you build cut and paste so that you don't have to re-invent the wheel again later down the road -

 

[RECrerar]

Glad you like it.

I'm assuming by portable you mean so I don't have to re-write the code everytime I want to use it. I was planning on keeping the task field names with the <Letter Number> convention so that wouldn't involve too much retyping, but it would be great if you could just cut and past as long as the tables were set up correctly and I'm sure better people than me could do that. However since I am not likely to be working on another database for a while, I think it will be quicker to make a bespoke code. You never know if I suddenly become fantastic as Access and the work takes less time than expected I may look at that.

Saying that Access is making much more sense today than at the start of the week so that is encouraging.

Congratulations on getting over 10,000 posts sometime today by the way (not sure if that's something to congratulate someone for, but it seems like some sort of milestone)