Access II 2007 Workshop
Query & Report
I. Review Tables/Forms
Ways to create tables: tables, templates & design Edit tables: new fields & table properties
Import option
Link tables: Relationship Forms
II. Queries
Create query using query wizard Create query from design view Add criteria
Export to Excel
Combine field information Group by query
Action queries
III. Reports
Create auto-report
Create report using report wizard
Create labels report
Access 2007 Page 1 I. Review of Tables & Forms:
Tables:
Open the Project Tracking database Click on the Create tab on the ribbon Review the various ways to create tables:
Create / Table
o Double click to add new field o New field button (field template)
Create / Table templates
o Select the desired template; i.e.: Contacts
Create / Table design
o Specify name, type & properties
Edit existing table:
Double click to open table
Click on Home/View/Design View Additional option to create table:
Import from an existing Excel file:
Click on External Data tab on the ribbon
From the Import group, click on Excel to import an excel spreadsheet Click browse to locate your file
Go to drive N and double click on folder compserv and double click on faculty.xls Click ok
Check the top box: First Row Contains Headings Click next twice
Click choose my own primary key (defaults to faculty id) Click next
Enter table name: tbl_faculty Click Finish and Close
Open the table to view your records Create “relationship” between two tables:
Click on Database Tools tab and click on Relationship
Review the existing relationship between Projects tables (ID field) and Tasks table (ProjectIDfield); One-to-many relationship
Forms:
Create a form:
Click on Create tab
Click once on table name from Navigation Pane & click on Form
Access 2007 Page 2 II. Queries
Queries allow you to select records based on criteria. You can also define which fields to display in your query. Advanced queries allow you to perform functions such as: enter parameters, concatenate fields and update records.
Create a Query using the Query Wizard (with 1 table):
1. Click on the Create tab on the ribbon 2. Click Query Wizard from the Other group 3. Select Simple Query Wizard and click ok
4. Under Table/Queries, click on the expand arrow and select tbl_project
5. Double click on the field names to select the desired fields or click on field name and press >
(select ID, Project Name, Dept, Dept.Value, Requestor_Firstname, Requestor_Lastname, Status & Priority)
6. Click next
7. Enter a title for the query: qry_projects_list 8. Click finish
Edit the Query:
1. From the query datasheet view, click on Home 2. Click on View/Design View
3. Use the following criteria to display records then click on Run (red !) to view results:
Display all records with dept = to IT “IT”
Display all records with dept = to IT or Reslife “IT” or “Reslife”
Display all records with dept = IT and status = “in progress” “IT” “in progress”
Display all records with Project Name beginning with “Web” “web*”
Display all records with Project Name containing “web” “*web*”
Display all records with any dept but IT Not = “IT”
Display all records that don’t have a begin date “Is Null”
Display all records that have dates in October Between #10/1/2008# And #10/31/2008#
4. To sort by a specific field:
Click in the sort row under the field name and select ascending 5. To sort by two fields:
The first field needs to be listed before the second field. If that is not how you want them listed in the output query, select the two fields again (i.e.: lastname then firstname), select ascending then deselect the show box on both fields.
Create a Query using the Design View (with 2 tables):
1. Click on the Create tab on the ribbon 2. Click Query Design from the Other group 3. Click add to add tbl_project
4. Click on tbl_tasks to select and click add
Access 2007 Page 3 5. Click Close to close the Show Table dialog box
6. Select the desired fields from the two tables:
a. From Projects: Project_Name, Dept and Person_Assigned b. From Tasks: Title, Status, Start Date and Due Date
7. Click Run from the ribbon to display your results
8. From the query results edit a record: Change person assigned and add a due date 9. Save the query as qry_project_detail
To run an existing query:
1. From the Navigation Pane, double click on the name of the query to run
Notes:
Click on View / Design View to make changes to your query Click on View / Datasheet View to view your query results Export your table results to Excel:
1. Run your query and view your results (your query must be saved) 2. Click on the External Data tab
3. From the Export group, click on Excel
4. Select the option to export data with formatting and layout
5. Click Browse to select a file location (change name if desired) and save / OK / close Create a query that combines field information:
1. Edit your query
2. Create a new field called NAME and use the following syntax to combine the last and first name fields:
3. NAME: [LastName] & ", " & [FirstName]
4. Run the query and view the results.
Create a summary query using the “group by” option. Find the total number of tasks assigned to each person:
1. Click on the Create tab on the ribbon 2. Click Query Design from the Other group 3. Click add to add tbl_project
4. Double click on tbl_tasks to add
5. Click close to close the Show Table dialog box 6. Select the desired fields from the two tables:
a. From Projects: Person_Assigned (include only the fields that make up your group) b. From Tasks: Title
c. Note: You can include other fields that will be used in the selection of the date but not display in the query results.
Access 2007 Page 4 7. Click on the Sigma button located under Query Tools / Design (Show/Hide section)
8. A new row in the grid section appears labeled: Total and group by is listed in the cells. To get a count of the number of tasks per person, click on the group by cell under task title and select Count.
9. Click Run to view your results
10. Save the query as qry_tasks per person
Edit the query and add criteria to only count tasks that are “not completed”:
1. Edit the query and add the field status from the task table by double clicking on it 2. Enter the criteria for status: not = completed
3. Change the default “group by” option listed for status to “Where”
4. Remove the check mark from the status column so it is used in selection only and does not display in the results
5. Run your query and view your results
Action Queries: Action queries allow you to perform an action on the selected records.
TYPES:
Make-table query - makes a new table from the selected records Update query - makes global changes to the selected records
Append query - copies records from one table and places then into another table Delete query - deletes the selected records from your table
Create a Make-Table query:
1. Click on the Create tab on the ribbon 2. Click Query Design from the Other group 3. Click add to add tbl_project
4. Select the fields you want for the new table
a. Note: Multi valued fields are not allowed in the make table query (use dept.value not dept)
5. Specify the criteria; status = completed
6. Run the query to make sure you get the expected records 7. Go back to the design view
8. Select Make Table from the Query Type section 9. Click Run !
10. Enter a table name and click ok 11. Click Run !
12. Click Yes to paste your records to the new table 13. Save the query
Open the new table to view a subset of your original table Open the original table (note the total records still remain)
Access 2007 Page 5 III. Reports
Reports allow you to define and output your data to the screen and printer.
Create a report to show all your records:
1. Click on Create tab from the ribbon
2. Click once on your table or query from the Navigation Pane 3. Click on Report button from the Report group
4. All your data will be presented in a report format in the Layout View 5. Notice the Report Layout Tools (on demand tab):
You will have additional options labeled: Format, Arrange and Page Setup 6. From the ribbon, make the following changes (in Layout View):
From the Format tab, click on Title in the controls group and add a new title
Click on ID and click and drag on vertical line to make the column narrower
Click on Gridlines and select horizontal lines
Click on the Page Setup tab and select Landscape
Click on Margins (Page Setup tab) and set desired margins
Click on Autoformat (more down arrow) and select a new format
To remove an autoformat, click on more arrow, select autoformat wizard and select None
Click on Add A Group (listed at bottom) and select person_assigned 7. Click on View / Report View to view your report
8. Close and save your report as rpt_projects
View types:
Report View – Allows you to view the report
Print Preview – Allows you to view the report and make page setup changes.
Layout View – Allows you to modify the report while viewing your current data.
Design View – Allows you to modify the report while viewing field labels and data names. Contains more control options than the layout view.
Create a report using Report Wizards (based on your saved query):
1. Click on Create tab from the ribbon
2. Click on Report Wizard button from the Report group
3. Click on the expand arrow under Tables/Queries and select a query (qry_project_detail) 4. Click Next
5. Click on the >> button to select all the fields from the query 6. Click Next
7. Click Next
8. Click Next on “Do you want to add any grouping”
a. Option: click on the field priority to add a group 9. Click Next
10. Click on the expand arrow in the first box and select title to sort the detail records by this field
Access 2007 Page 6 11. Click next
12. Select Landscape as the orientation and click Next 13. Select the desired style and click Next
14. Enter the report title as rpt_project_detail 15. Click Finish to preview the report
Change the report in Design View:
1. Open the report and click on Home / View / Design View
2. Locate the Detail band then click on a field located below this band
3. Make the font italic and one font size larger: from Report Design Tools/Design/Font 4. Click on the report header, then click on the field border (four headed arrow appears) and
move it to the center of the page
5. Widen a column: point to the right field border and click and drag when you see the 6. To change the cell height: point to the bottom field border and click and drag when you
see the double headed arrows.
7. To change only one cell: Click on Arrange tab then click on Remove (4th icon from left).
Click on one cell to select then use the arrows to change the cell size.
Click in the cell and drag to reposition the cell.
Create labels:
1. Click on Create tab from the ribbon
2. Click on the Faculty table from the Navigation Pane 3. Click on Labels button from the Report group 4. Specify the label size and click next
5. Specify font name, size, weight and color and click next 6. Design the prototype label:
a. Double click on firstname and press space b. Double click on lastname and press return c. Add additional fields
7. Press next
8. Double click on the sort field 9. Click finish / ok
To Run Label Report:
1. Double click on label report name (labels are shown in one column)
2. Click on Click on View / Print Preview (to see multiple columns of labels) / OK