Learning Microsoft Access 2007 - Queries
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Learning Microsoft Access 2007 - Queries as PDF for free.
More details
- Words: 1,106
- Pages: 9
Learning Microsoft Access 2007 By Greg Bowden
Chapter 3
Introduction to Queries
Guided Computer Tutorials www.gct.com.au
PUBLISHED BY GUIDED COMPUTER TUTORIALS PO Box 311 Belmont, Victoria, 3216, Australia www.gct.com.au © Greg Bowden This product is available in Single or Multi User versions. Single-user versions are for single person use at any particular time, just as a single text book would be used. If you intend to use the notes with multiple students the single user version should be upgraded to the multi-user version. Multi-user versions allow the school or institution to print as many copies as required, or to place the PDF files on the school network, intranet and staff laptops. A certificate of authentication is provided with multi-user versions. Bookmarks provide links to all headings and sub-headings, and individual chapters are provided.
First published 2008 ISBN: 1 921217 52 9 (Module 1) 1 921217 53 7 (Module 2) PDF document on CD-ROM
Every effort has been made to ensure that images used in this publication are free of copyright, but there may be instances where this has not been possible. Guided Computer Tutorials would welcome any information that would redress this situation.
Chapter
3
Introduction to Queries Queries allow you to ask questions of your database. You can combine data from different tables, permanently find or sort data, do calculations or produce interactive dialogue boxes to allow a user to enter search data. There are many different types of Queries, in this chapter you will be introduced to the most common type, SELECT QUERIES. A sample database has been prepared for you. It records employee information in two tables. One table records the employee’s personal details, the second table records the employee’s pay details. The following diagram summarises the structure of the two tables. Personal Details Employee ID First Name Last Name Address
Pay Details Employee ID Position Section Weekly Hours Pay Rate
Notice that the field EMPLOYEE ID is present in both tables. This field is called the PRIMARY KEY and it enables two tables to be linked so that data can be used from both of them in the Query.
Loading the Sample File 1
Load Microsoft Access and click on the OPEN icon in the OFFICE BUTTON or QUICK ACCESS TOOLBAR. 2 Access the CHAPTER 3 folder of the ACCESS 2007 SUPPORT FILES and click on the CHAPTER 3 WAGES file.
3 Click on the arrow next to the OPEN button and select OPEN READ-ONLY.
© Guided Computer Tutorials 2008
3-1
Learning Microsoft Access 2007
4
Click on the OFFICE BUTTON, highlight SAVE AS and select ACCESS 2007 DATABASE.
5
Access your STORAGE folder and save the database as: CHAPTER 3 WAGES 1.
6
Turn off the SECURITY ALERT bar if you wish
Looking at the Tables 1
Open each of the two tables in turn to become familiar with the data stored in them.
2
Close the tables by clicking in their close boxes.
Creating a Query
1 Open the CREATE tab in the RIBBON and click on the QUERY DESIGN icon to start the query.
2 The QUERY design screen is opened with the SHOW TABLE dialogue box displayed.
3-2
© Guided Computer Tutorials 2008
Introduction to Queries 3
3
Before the Query can be constructed the tables used by the Query need to be set.
4 Click on the PERSONAL DETAILS table followed by the ADD button to add it to the Query.
5 Click on the PAY DETAILS table and ADD it to the Query frame.
6
Close the SHOW TABLE dialogue box by clicking in its close box.
© Guided Computer Tutorials 2008
3-3
Learning Microsoft Access 2007
A Linking the Tables In order to create a query on multiple tables, the tables needed to be linked together using the PRIMARY KEY fields. In this case the EMPLOYEE ID field is present in both tables and it has been set as the PRIMARY KEY field in each table.
The link between the tables has been created for you.
NOTE
If the LINK or (RELATIONSHIP) line is not present in your tables, click on the EMPLOYEE ID field in the PERSONAL DETAILS table and drag the field over the EMPLOYEE ID field in the PAY DETAILS table.
B Building the Query Let’s create a query that displays the employee’s name, weekly hours and pay rate.
1 Move the pointer over the FIRST NAME field in the PERSONAL DETAILS table. Hold down the mouse button, drag the field to the first field cell in the query.
2 Notice that the table that the field came from is listed in the TABLE row of the query.
3-4
© Guided Computer Tutorials 2008
Introduction to Queries
3
3 Drag the LAST NAME field in to the second FIELD cell in the query.
4 Move the pointer over the WEEKLY HOURS field in the PAY DETAILS table and drag it into the third field cell in the query.
5 Double click on the PAY RATE field. This is another way of inserting a field into the next column of a query.
© Guided Computer Tutorials 2008
3-5
Learning Microsoft Access 2007
C Looking at the Query 1 Click on the VIEW icon at the left of the DESIGN tab of the RIBBON (not the arrow at the base of the icon).
2 The screen changes to DATASHEET VIEW showing data from both tables.
3
Return to DESIGN VIEW by clicking the arrow at the base of the VIEW icon in the RIBBON and selecting DESIGN VIEW.
D Adding a Calculation Field We can work out how much pay each employee receives by adding a calculation field in the fifth query field cell. 1
Click in the fifth field cell (next to PAY RATE).
2
We could type the formula directly into the cell, however, the formula will not fit and it becomes difficult to see. To overcome this problem we can zoom in on the cell. Hold down the SHIFT key and press the F2 key to zoom the cell.
3-6
© Guided Computer Tutorials 2008
Introduction to Queries 3
3
Enter: Total Pay: [Weekly Hours] * [Pay Rate]
Pay Rate field in square brackets. Multiplication sign. Weekly Hours field in square brackets. Name of the calculation field followed by a colon (:)
NOTE:
i The calculation field name needs the colon symbol (:) directly after the field name.
ii Fields used within calculation fields are enclosed in square brackets [].
iii Check that you have typed the field names correctly.
4 Select OK to accept the formula and it is placed in the FIELD cell.
© Guided Computer Tutorials 2008
3-7
Chapter 3
Introduction to Queries
Guided Computer Tutorials www.gct.com.au
PUBLISHED BY GUIDED COMPUTER TUTORIALS PO Box 311 Belmont, Victoria, 3216, Australia www.gct.com.au © Greg Bowden This product is available in Single or Multi User versions. Single-user versions are for single person use at any particular time, just as a single text book would be used. If you intend to use the notes with multiple students the single user version should be upgraded to the multi-user version. Multi-user versions allow the school or institution to print as many copies as required, or to place the PDF files on the school network, intranet and staff laptops. A certificate of authentication is provided with multi-user versions. Bookmarks provide links to all headings and sub-headings, and individual chapters are provided.
First published 2008 ISBN: 1 921217 52 9 (Module 1) 1 921217 53 7 (Module 2) PDF document on CD-ROM
Every effort has been made to ensure that images used in this publication are free of copyright, but there may be instances where this has not been possible. Guided Computer Tutorials would welcome any information that would redress this situation.
Chapter
3
Introduction to Queries Queries allow you to ask questions of your database. You can combine data from different tables, permanently find or sort data, do calculations or produce interactive dialogue boxes to allow a user to enter search data. There are many different types of Queries, in this chapter you will be introduced to the most common type, SELECT QUERIES. A sample database has been prepared for you. It records employee information in two tables. One table records the employee’s personal details, the second table records the employee’s pay details. The following diagram summarises the structure of the two tables. Personal Details Employee ID First Name Last Name Address
Pay Details Employee ID Position Section Weekly Hours Pay Rate
Notice that the field EMPLOYEE ID is present in both tables. This field is called the PRIMARY KEY and it enables two tables to be linked so that data can be used from both of them in the Query.
Loading the Sample File 1
Load Microsoft Access and click on the OPEN icon in the OFFICE BUTTON or QUICK ACCESS TOOLBAR. 2 Access the CHAPTER 3 folder of the ACCESS 2007 SUPPORT FILES and click on the CHAPTER 3 WAGES file.
3 Click on the arrow next to the OPEN button and select OPEN READ-ONLY.
© Guided Computer Tutorials 2008
3-1
Learning Microsoft Access 2007
4
Click on the OFFICE BUTTON, highlight SAVE AS and select ACCESS 2007 DATABASE.
5
Access your STORAGE folder and save the database as: CHAPTER 3 WAGES 1.
6
Turn off the SECURITY ALERT bar if you wish
Looking at the Tables 1
Open each of the two tables in turn to become familiar with the data stored in them.
2
Close the tables by clicking in their close boxes.
Creating a Query
1 Open the CREATE tab in the RIBBON and click on the QUERY DESIGN icon to start the query.
2 The QUERY design screen is opened with the SHOW TABLE dialogue box displayed.
3-2
© Guided Computer Tutorials 2008
Introduction to Queries 3
3
Before the Query can be constructed the tables used by the Query need to be set.
4 Click on the PERSONAL DETAILS table followed by the ADD button to add it to the Query.
5 Click on the PAY DETAILS table and ADD it to the Query frame.
6
Close the SHOW TABLE dialogue box by clicking in its close box.
© Guided Computer Tutorials 2008
3-3
Learning Microsoft Access 2007
A Linking the Tables In order to create a query on multiple tables, the tables needed to be linked together using the PRIMARY KEY fields. In this case the EMPLOYEE ID field is present in both tables and it has been set as the PRIMARY KEY field in each table.
The link between the tables has been created for you.
NOTE
If the LINK or (RELATIONSHIP) line is not present in your tables, click on the EMPLOYEE ID field in the PERSONAL DETAILS table and drag the field over the EMPLOYEE ID field in the PAY DETAILS table.
B Building the Query Let’s create a query that displays the employee’s name, weekly hours and pay rate.
1 Move the pointer over the FIRST NAME field in the PERSONAL DETAILS table. Hold down the mouse button, drag the field to the first field cell in the query.
2 Notice that the table that the field came from is listed in the TABLE row of the query.
3-4
© Guided Computer Tutorials 2008
Introduction to Queries
3
3 Drag the LAST NAME field in to the second FIELD cell in the query.
4 Move the pointer over the WEEKLY HOURS field in the PAY DETAILS table and drag it into the third field cell in the query.
5 Double click on the PAY RATE field. This is another way of inserting a field into the next column of a query.
© Guided Computer Tutorials 2008
3-5
Learning Microsoft Access 2007
C Looking at the Query 1 Click on the VIEW icon at the left of the DESIGN tab of the RIBBON (not the arrow at the base of the icon).
2 The screen changes to DATASHEET VIEW showing data from both tables.
3
Return to DESIGN VIEW by clicking the arrow at the base of the VIEW icon in the RIBBON and selecting DESIGN VIEW.
D Adding a Calculation Field We can work out how much pay each employee receives by adding a calculation field in the fifth query field cell. 1
Click in the fifth field cell (next to PAY RATE).
2
We could type the formula directly into the cell, however, the formula will not fit and it becomes difficult to see. To overcome this problem we can zoom in on the cell. Hold down the SHIFT key and press the F2 key to zoom the cell.
3-6
© Guided Computer Tutorials 2008
Introduction to Queries 3
3
Enter: Total Pay: [Weekly Hours] * [Pay Rate]
Pay Rate field in square brackets. Multiplication sign. Weekly Hours field in square brackets. Name of the calculation field followed by a colon (:)
NOTE:
i The calculation field name needs the colon symbol (:) directly after the field name.
ii Fields used within calculation fields are enclosed in square brackets [].
iii Check that you have typed the field names correctly.
4 Select OK to accept the formula and it is placed in the FIELD cell.
© Guided Computer Tutorials 2008
3-7
Related Documents
Learning Microsoft Access 2007 - Queries
May 2020 11
Learning Microsoft Access 2007 - Macros
May 2020 15
Learning Microsoft Access 2007 - Forms
May 2020 18
Learning Microsoft Access 2007 - Invoices
May 2020 14
Learning Microsoft Access 2007 - Introduction
May 2020 13
Learning Microsoft Access 2007 - Buttons
May 2020 13More Documents from "Guided Computer Tutorials"
Gct Product Catalogue
May 2020 24
Learning Microsoft Access 2007 - Forms
May 2020 18
Learning Microsoft Word 2007 - Tabs
May 2020 24
Learning Adobe Dreamweaver Cs4 - Anchor Points
May 2020 20
