SharePoint
has no simple way to handle recurring tasks, but by letting
an Access query update a To Do list you can still make it
easy to both remember recurring tasks and to verify that
the tasks have been done.
In the first demo below Peter Kalmström, CEO and Systems
Designer of kalmstrom.com Business Solutions, exports an
Excel table with info about tasks, assigned people and due
dates to a SharePoint list. This list is called Every Month
and shows tasks that have to be done repeatedly each month.
It has columns for What, Who and Day of Month.
Then Peter creates a To-Do list and copies the What and
Who data from the Every Month list into the Task Name and
Assigned To fileds in the To Do-list.
With the method shown above, the Due date column cannot
be copied as easily as the other columns. Furthermore, you
have to copy and paste recurring tasks for every month.
Create Access queries that add recurring tasks into
a SharePoint list
A better solution is to let Microsoft Access handle the
update with a query. That is what Peter shows in the demo
below, where he creates a query that takes data from the
Every Month list and the UserInfo list and appends it to
the To Do list.
The UserInfo list is a hidden SharePoint list that becomes
visible when you link Access to the SharePoint site. When
you select to use a list that has an Assigned To field,
Access creates a linked UserInfo table from the UserInfo
list as well, even if you don't check the box for it.
Open a blank desktop database and close the default
table.
Under the EXTERNAL DATA tab, click on More and select
SharePoint list, to link the two recurrent tasks lists
to Access. Give the URL to the site, and use the Linked
table option. Click on Next.
Check the Every Month list and the To Do list. Click
OK, and two new tables with the same names and content
as the lists will be created in the database. A linked
UserInfo table will also be created.
Under the CREATE tab, click on the Query Design
button. Add the Every Month table, and select the What
and Who fields. (Add by double clicking on the field
or by clicking on the Add button.)
Under the DESIGN tab, click on the Append button.
Append the What field in the Every Month table to the
Task Name in the To Do table.
Right click in the query pane and select Show Table...
Add the UserInfo table.
Connect the Who field in the Every Month table to
the ID of the UserInfo. This will be shown in the second
column in the Query, and you can append the Assigned
To field.
To have the Due Date info into the To Do table,
select the Every Month table and click on the Parameters
button under the DESIGN tab.
Enter Year and Month. Both should be Integers.
Click on the Builder button under the DESIGN tab.
Select Functions, Built-in Functions, Date/Time
and Date Serial.
Still in the Builder, select the query you just
created and the parameters Year and Month.
For the day, select the Every Month table and Day
of Month. Click on Value and then OK.
Now the expression you have built will be filled
out in the third query column, and you can change the
text "Expr1" into Due Date and append the Due Date field.
Click on the Run button under the DESIGN tab to
run the query. You will now be asked to enter values
for the parameters Year and Month. Use numbers for both.
Run the query for every month and year, to have
the SharePoint To Do list updated with all the recurring
tasks.