I am looking for some guidance please on what I need to do to create a workflow management and record keeping tool for my database.
I am using Access 2010. The DB has a table called "Tasks" and each task therein is linked to a frequency (e.g. daily, monthly, adhoc etc). I have created a query that correctly returns all the tasks due on any date.
What I want the DB to do is the following:
1) Produce a list of tasks due today and for the user to be able to check them off as done and add any other information (e.g. how long they took to do) against each task record.
2) That data should then be saved separately.
3) When requesting the list of tasks, I would like it to be able to include any tasks that were due yesterday but not completed too. Also, if this query is run more than once per day then it ought to first pull in the most recent information on any task that has been completed in steps 1) & 2) above to avoid duplication.
4) When saving the data in step 2), I would want it to intelligently either update the currently saved information (if the specific task record is already saved) or append that information (if the task record is not currently in the saved data for that date).
I would greatly appreciate it if someone could suggest what tables/queries/macros I will need and the purpose of each. I am very familiar with Excel VBA and happy to get my hands dirty with any Access VBA I may need to learn but my knowledge of DBs and SQL is very much at the noob level.
I would start with a way to do some evaluation. You have some tasks with due dates assigned so you need a way to evaluate if they are due now, if they were due yesterday or if they are due next week and so on. There are several ways to approach this but what I have done in the past is to add a couple fields to my query. One field you could label as "Current Date" or something and use the Date() function. This field would always have the current date in it when you run it. If you add another field that would take the due date and subtract it from the current date, the result would be the number of days until the task is due. A negative number would mean its late. That would give you the ability to run a query that would show you late tasks, tasks due today, tasks due this week...and on and on.
That's just were I would start.
For completing tasks, you could add a field to your table as something like "Status" and make the options for that field Open or Closed. If they are closed, you can exclude them from your queries