This topic covers how to create a Microsoft Access report for a SharePoint Task List. In this example, our sample task list resides in a sub-site based on a standard Team Site template, as shown here.
Create a SQL query statement from SQL View and use that query in a Microsoft Access report, running from your desktop. Go to the page where SQL View is installed, generate the SQL query statement by completing the following:
|1.||In the Select Site drop-down list, choose the site where the Task list was created.|
|2.||Select your task list in the Select List drop-down menu.|
|3.||Enter a name in the View Name text box. To create a view in the SharePoint Content database, this view name must be unique. If a view with a similar name already exists, then the web part identifies it and view is not created. Note that the existing views are not deleted or dropped.|
|4.||Select the Validate Lookup Fields check box so the query will perform the joined query to get the name of the assigned user.|
|5.||Select Fields you want returned in the query.|
|6.||Leave the Generate View in Database check box clear.|
|7.||Click the Generate button.||
Copy the SQL statement result to the windows clipboard starting from the SELECT statement.
|8.||Open Microsoft Access and select Project using existing data… from the wizard menu. The first thing you have to do is set the name of the project. Then the next screen asks for the connection information.|
|9.||In this example, we will set the connection to use Windows Authentication, the name of the SharePoint server, and the name of the SharePoint content database. Make sure to test the connection to the database before you click OK.|
|10.||After a few moments, the wizard finishes and you can see the tables and views within the SharePoint content database.|
|11.||Navigate to the Report tab in the control panel on the left. You can use a wizard to build the report or just create the report in Design View by clicking on Create Report in Design View. The report and its properties and events sheets are displayed. Click on the Data tab and enter the SQL query statement (saved in step 7) in the Record Source property.|
|12.||Once the SQL statement is inserted, a box displays with all the fields in the Select statement so that it can be dragged onto the report.|