Creating Queries in Access Exercises



UCL

Education & information support division

information systems

Access 2003

Creating Access

Queries

Exercises

Content

Task One: simple select queries 2

Task Two: using wildcards 2

Task Three: using AND and OR 2

Task Four: using date and number criteria 3

Task Five: null values and excluding data 3

Task Six: parameter queries 4

Task Seven: grouping and aggregate functions 4

Task Seven: grouping and aggregate functions 4

Task Eight: grouping and aggregate functions 4

These tasks should be completed using the following database: exercises.mdb. Answers can be found in exercises-queries-answers.mdb. Files can be downloaded from the web page: ucl.ac.uk/is/documents/

Task One: simple select queries

1. Create a query based on the tbl_Employees table.

2. Display the following fields: First_Name, Last_Name, Job_Title, Telephone, Nationality, Dept_ID.

3. Select all employees who are American.

4. Sort by Last_Name.

5. Save your query with the name qry_17-1.

Additional tasks:

6. Create a second query based on the tbl_Employees table.

7. Display the following fields in this order: First_Name, Last_Name, Job_Title, Telephone.

8. Select all employees who work in the Sales Department (Dept_ID = 100).

9. Do not display the Dept_ID field in the query results.

10. Sort by Job_Title and then by Last_Name, but do not change the order of the fields.

11. Save your query with the name qry_17-2.

Task Two: using wildcards

1. Create a query based on the tbl_Employees table. Select all employees whose surnames begin with the letter “W”. Decide for yourself which fields to display. Save your query with the name qry_18-1. How many records are displayed in the query results?

2. Create a query based on the tbl_Employees table. Select all employees with Manager in their job title. Decide for yourself which fields to display. Save your query with the name qry_18-2. How many records are displayed in the query results?

Task Three: using AND and OR

1. Create a query based on the tbl_Employees table. Show details for all employees who are either managers or who work in the Sales Department (Dept_ID = 100). Save your query with the name qry_19-1.

2. Create a query based on the tbl_Employees table. Show details for all employees who are managers in the Sales Department. Save your query with the name qry_19-2.

3. What type of logical operator does each of the above two queries use? Which of the two queries returns the most records and why?

Additional tasks:

4. Create a query based on the tbl_Employees table. Show details for all employees who are officers in either the Finance Department (Dept_ID = 300) or the Personnel Department (Dept_ID = 500). How many records are displayed in the query results? Save your query with the name qry_19-3.

Task Four: using date and number criteria

1. Create a query based on the tbl_Employees table. Select all employees who were born before 1950. Decide for yourself which fields to display. Save your query with the name qry_20-1.

2. Create a second query based on the tbl_Employees table. Select all employees who were born during the 1960s. Decide for yourself which fields to display. Save your query with the name qry_20-2.

Additional tasks:

3. What criterion did you use in step 2 above? How many different ways can you find of displaying the same results?

4. Create a third query based on the tbl_Employees table. Select all employees who were born in the month of April. Decide for yourself which fields to display. Save your query with the name qry_20-3.

Task Five: null values and excluding data

Create a query which includes all the fields from tbl_Employees. For each of the following tasks, add criteria to find the appropriate employees, view the results in Datasheet view and then modify the Criteria rows to carry out the next task.

1. All employees who do have a disability. (qry_23-1)

2. All employees who do not have a disability. (qry_23-2)

3. All employees who do not have a telephone number. (qry_23-3)

4. All employees who do have a telephone number. (qry_23-4)

5. All employees who are not in the Sales department. (qry_23-5)

6. All employees who are not managers. (qry_23-6)

Additional tasks:

7. All employees who are either officers or assistants. (qry_23-7)

8. All employees who are neither officers nor assistants. (qry_23-8a and b)

Which of the two answers given is correct?

Task Six: parameter queries

1. Create a query based on the tbl_Employees and tbl_Departments tables. Display the following fields: Dept_ID, Dept_Name, First_Name, Last_Name, Job_Title, Telephone, Dept_Town. Enter a parameter criterion in the Dept_Town field. Save your query with the name qry_22-1. Display results for different towns.

2. Create a query based on the tbl_Employees and tbl_Departments tables. Decide for yourself which fields to display. Enter a parameter criterion in the Dept_Name field. Save your query with the name qry_22-2. Display results for different departments.

Additional tasks:

3. Create a query based just on the tbl_Employees table. Decide for yourself which fields to display. Enter parameter criteria in the DOB field to find employees born between two dates (you will need two parameters). Save your query with the name qry_22-3. Display results for different date ranges.

Please note that the answer to 3 is not in exercises-queries-answers.mdb at the moment.

Task Seven: grouping and aggregate functions

1. Create a query based on the tbl_Employees and tbl_Departments tables. Display the following fields: Dept_ID, Dept_Name and Staff_ID.

2. Run the query to view the results. Which values are repeated?

3. Add grouping and aggregate functions to the query to enable you to view the number of employees in each department:

• Add grouping to the fields from tbl_Departments

• Add a Count function to Staff_ID

4. Display the results of the query.

5. Save the query as qry_25-1.

Task Eight: grouping and aggregate functions

1. Create a query based on tbl_Employees and tbl_Salaries

2. Select appropriate fields and appropriate grouping and aggregate functions to display the Salary_Date when each employees salary was last increased.

3. Add criteria so that only salaries under £20 000 are displayed.

4. Change the column heading of the Salary_Date field to ‘Last Salary Increase’.

5. Save the query as qry_25-2.

-----------------------

Text box

Text box

Text box

Text box

WebCT

WebCT ( Version 1 ( June 2003

WebCT ( Version 1 ( June 2003

for Designers

ox

Text box

Text box

Text bWebCT for Designers

WebCT for Designers

ox

Text box

or Designers

ox

box

How does Auto-Summarise work?

AutoSummarize identifies the key points in a document for you to share with others or quickly scan.

AutoSummarize works best on well-structured documents such as reports, articles, and scientific papers.

It determines key points by analyzing the document and assigning a score to each sentence. Sentences that contain words used frequently in the document are given a higher score. You then choose a percentage of the highest-scoring sentences to display in the summary.

WebCT ( Version 1 ( June 2003

WebCT ( Version 1 ( June 2003

for Designers

ox

WebCT

WebCT ( Version 1 ( June 2003

WebCT ( Version 1 ( June 2003

for Designers

ox

Text box

Text box

Text bWebCT for Designers

WebCT for Designers

ox

Text box

Text box

Text box

Text bWebCT for Designers

WebCT for Designers

ox

Text box

WebCT ( Version 1 ( June 2003

WebCT ( Version 1 ( June 2003

WebCT for Designers

WebCT for Designers

How does Auto-Summarise work?

AutoSummarize identifies the key points in a document for you to share with others or quickly scan.

AutoSummarize works best on well-structured documents such as reports, articles, and scientific papers.

It determines key points by analyzing the document and assigning a score to each sentence. Sentences that contain words used fre

This document is intended for

It covers the essentials of

Special attention is given to

WebCT ( Version 1 ( June 2003

This document is intended for

It covers the essentials of

Special attention is given to

WebCT ( Version 1 ( June 2003

he document are given a higher score. You then choose a percentage of the highest-scoring sentences to display in the summary.

rs

ox

How does Auto-Summarise work?

AutoSummarize identifies the key points in a document for you to share with others or quickly scan.

AutoSummarize works best on well-structured documents such as reports, articles, and scientific papers.

It determines key points by analyzing the document and assigning a score to each sentence. Sentences that contain words used frequently in the document are given a higher score. You then choose a percentage of the highest-scoring sentences to display in the summary.

How does Auto-Summarise work?

AutoSummarize identifies the key points in a document for you to share with others or quickly scan.

AutoSummarize works best on well-structured documents such as reports, articles, and scientific papers.

It determines key points by analyzing the document and assigning a score to each sentence. Sentences

WebCT ( Version 1 ( June 2003

WebCT ( Vers

Word XP

Word XP AutoSummarize Function ( Version 1 ( July 2003

AutoSummarize Function

ne 2003

er score. You then choose a percentage of the highest-scoring sentences to display in the summary.

WebCT ( Version 1 ( June 2003

WebCT ( VeWebCT for DeFigure 4

x

x

x

x

Using AutoSummarize as a teach tool

x

x

x

x

x

Tip:

d down the [Ctrl] key while selecting the files.

Tip:

d down the [Ctrl] key while selecting the files.

s

How does Auto-Summarize work?

AutoSummarize identifies the key points in a document for you to share with others or quickly scan.

AutoSummarize works best on well-structured documents such as reports, articles, and scientific papers.

It determines key points by analyzing the document and assigning a score to each sentence. Sentences that contain words used frequently in the document are given a higher score. You then choose a percentage of the highest-scoring sentences to display in the summary.

AutoSuFigure 10

0

Figure 11

This d Create a new

document and put the summary there

Figure 8

Insert an executive summary or abstract at the top of the document

Highlight key points

Figure 9

Hide everything but the summary without leaving the original document

Use the AutoSummarize function to "create a new document" based on 20% of the original.

Print off the summary and score it out of ten for (i) clarity of key ideas (ii) fluency of summary.

Create your own version, improving on the auto-summary without increasing the word length.

Figure 3

Figure2

Tip:

Brackets can be used in file names. For example, the second version of a document could be named...

Pakistan Summary(v2)

or revision purposes.

bCT ( Version 1 ( June 2003

Word XP (Word 2002)

Figure 12

AutoSummarize Function

Figure 5

Use AutoSummarize as a teaching tool

Highlight key points

Insert a summary

Create an abstract

Use word count

more/…

ng sentences to display in the summary.

rs

ox

Tip:

Figure 8

Insert an executive summary or abstract at the top of the document

Highlight key points

Figure 9

Hide everything but the summary without leaving the original document

Use the AutoSummarize function to "create a new document" based on 20% of the original.

Print off the summary and score it The AutoSummarize dialogue box

fluency of summary.

Create your own version, improving on the auto-summary without increasing the word length.

the [Ctrl] key while selecting the files.

Tip:

d down the [Ctrl] key while selecting the files.

Figure 1

Percent of Original

This d Create a newFigure 11

Insert an executive summary or abstract at the top of the document

Highlight key points

Figure 13

Hide everything but the summary without leaving the original document

Use the AutoSummarize function to “create a new document” based on 20% of the original.

Print off the summary and score it out of ten for (i) clarity of key ideas (ii) fluency of summary.

Create your own version, improving on the auto-summary without increasing the word length.

Figure 3

Figure 2

Tip:

Brackets can be used in file names. For example, the second version of a document could be named...

Pakistan Summary (v2)

Figure 8

Figure 7

Figure 6

This document is intended for both UCL staff and students who wish to create

a summary, or abstract, of text from a Word document.

It covers the essentials of using the AutoSummarize function in Microsoft® Word XP (Word 2002).

It does not guarantee the quality of the result of the AutoSummarize function which

should always be proofread for both sentence construction and content by the creator of that summary, or abstract.

The AutoSummarize dialogue box

ary.

Create your own version, improving on the auto-summary without increasing the word length.

Figure 3

Figure2

Tip:

Brackets can be used in file names. For example, the second version of a document could be named...

Pakistan Summary(v2)

or revision purposes.

bCT ( Version 1 ( June 2003

Tip:

Figure 8

Insert an executive summary or abstract at the top of the document

Highlight key points

Figure 9

Hide everything but the summary without leaving the original document

Use the AutoSummarize function to "create a new document" based on 20% of the original.

Print off the summary and score it The AutoSummarize dialogue box

fluency of summary.

Create your own version, improving on the auto-summary without increasing the word length.

the [Ctrl] key while selecting the files.

Tip:

d down the [Ctrl] key while selecting the files.

Tip:

Figure 8

Insert an executive summary or abstract at the top of the document

Highlight key points

Figure 9

Hide everything but the summary without leaving the original document

Use the AutoSummarize function to "create a new document" based on 20% of the original.

Print off the summary and score it The AutoSummarize dialogue box

fluency of summary.

Create your own version, improving on the auto-summary without increasing the word length.

the [Ctrl] key while selecting the files.

Tip:

d down the [Ctrl] key while selecting the files.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download