Microsoft Office/Create queries for a database

Vocabulary

• Parameter
• Wildcards
• Compound Criteria
• Sort Key
• Major Key / Primary Sort Key
• Minor Key / Secondary Sort Key

Lesson

What is a Query

A query is a way of asking the database some questions.

• What clients' name begins with "M"?
• What clients are located on Main Street?
• What clients have paid more than $100.00?
• What clients belong to a certain employee?
• What client names begin with "M" and has paid more than $100.00?

Simple Query Wizard

• Go to the CREATE Tab
• Go to the OTHER group on the far right
• Click on Query Wizard
• This is just like creating a report
  • Pick the table you want to query
  • Pick the fields you want to look at
  • Click NEXT
  • Type in the title of the Query
  • Click FINISH

Use the drop downs at the top of each column to sort, or limit the data.

Criteria Query

• Click on View
• Click on Design View

• Go to the Criteria for the Client Number and type in BOKE
• Click the exclamation point for RUN at the top
• The query will return only the records with an employee number of BOKE.

Wildcards in Query

You use a wildcard to fill in for characters that you don't know. If you wanted to find a client whose name began with B you could do a query for B* like below.

Then when you run the query you get the results:

You can also use a question mark for a wildcard. A question mark replaces one character only. The asterisk replaces a group of characters. If you had employees Tom and Tim and wanted to list all of their clients you could do this by typing in T?M into the criteria field.

AND Criteria Query

You can fill in multiple criteria from one column to the next and this will take on an AND relationship.

Here we are querying companies that are on Main Street and paid more than $100.00

OR Criteria Query

If you move one of the query criteria down one row to the OR field you create an OR Criteria.

This query will list all the companies that are on Main Street OR paid more than $100.00

Parameter Query

A parameter query is one that lets the user answer the question each time it is run to get to the records that they want. This is very useful if a help desk person needs to access the records of a client each time they call to look up information quickly.

To set up this query you will need to type [] into the criteria field.

The program will then prompt the user to enter the Employee number when it is run and the following Pop-up will be displayed.

The user would type in an employee number such as EJ and click OK

Access would only display the records for employee number EJ

Top Values Query

A top value query is a query that sorts your fields from ascending to descending.

• Go to Design View on your Query
• Go to the Design Tab
• Change the Return box to the number of records you want
• Change one of the items to be sorted
• Run the query

Join Query

A join query is a query that contains data from multiple tables, those tables are all inter-connected through various relationships.

• Click the Create Tab
• Go to the Other Group
• Click on Query Design
• Click on the tables and then ADD, one at a time
• Make sure the tables are RELATED - have a line joining them
  • If they do not go to the Database Tools Tab and fix the relationship
  • Or make sure the names are typed exactly the same
  • Make sure the data types are the same
  • Make sure the field size is the same
• Click and drag fields from each table into the query
• Click on RUN

Calculation Query

Sometimes you will want to calculate a value from values that you already have.

• Create a query that has more than one either number, or currency field
• Go to Design View
• Go to the first column after all the fields you have already created
• Right click in the Field row
• Click on Zoom
• Type in the name of the column and then a colon
• Type in the field names in [] and then type the operation between the fields, do not put in any spaces
  • total cost:[amount owed]-[amount paid]
• Click OK
• Run the Query and then save the file in the required location

Calculate Statistics

• Create a query of Employee Number and Amount Paid from the client table
• Go to Design View
• Click on the TOTAL button at the top in the ribbon
• In the total row under Employee Number make sure it says Group By
• In the total row under Amount Paid change it to SUM or AVG
• Run the Query

Crosstab Query

A crosstab query is a query that lets you specify which fields contain row headings, which field contains column headings, and which field contains values to summarize.

• Click Create TAB
• Click Query Wizard
• Choose Crosstab Query and OK
• Click the table you want to work with - Client and click OK
• Move the Type of Advertisement to the Selected Fields side
• Click Next
• Click Employee Number
• Click Next
• Click Amount Due under field
• Click SUM under Function
• Click Next
• Type in a name for this query
• Click Finish

Sorting

There are two ways to sort your query:

From the query you can click the drop down and choose ascending or descending.

From the Design View you can choose different fields. The field to the left is the primary sort key.

In this shot this will sort by the Clients and then by how much they have paid

Sort and Omit Duplicates

To omit duplicates, 1st. You will need to click the box or field right of the row that you wish to omit duplicates, then you will need to open the Properties Sheet from the ribbon. Then go to the Unique Values item and change that to YES.

Project

Project 2 Creating Queries: Create the following Queries and print the results for your business:

1. Create a simple query on your table that contains the 10 records.
   1. Use the drop down to limit the data displayed
   2. Print the results
   3. Write on the paper what you did, in a full sentence so I can read and understand it.
2. Use the Query you created for #1
   1. Do an AND criteria query
   2. Print the results
   3. Write on the paper what you did, in a full sentence so I can read and understand it.
3. Use the Query you created for #1
   1. Do an OR criteria query
   2. Print the results
   3. Write on the paper what you did, in a full sentence so I can read and understand it.
4. Create a parameter query
   1. Show the teacher that it works
5. Create a Top Value Query from one of your tables
   1. Print the results
   2. Write on the paper what you did, in a full sentence so I can read and understand it.
6. Use the Query you created for #1
7. Create a join table query that uses your sales/invoices/appointments table
   1. Pull in all data from the different tables to show what is needed for the document
   2. Add a calculation for the total
   3. Create a report from the query
   4. Print the report
8. Staple all the items together
9. Make sure you name is on it

Rubric

Create Queries for Business

Standards	1 point	2 points	3 points	4 points	5 points
Create a Simple Query	Creates a Query on the 10 record table. It is printed with the students name on it.	Creates a Query on the 10 record table that uses a drop down to limit the data. It is printed with the students name on it.	Creates a Query on the 10 record table that uses a drop down to limit the data. It is printed with the students name on it and an attempt to explain what their query represents.	Creates a Query on the 10 record table that uses a drop down to limit the data. It is printed with the students name on it and an explanation of what their query represents.	Creates a Query on the 10 record table that uses a drop down to limit the data. It is printed with the students name on it and an accurate explanation of what their query represents.
Creates a Query that uses AND logic	Creates a Query on their data and then tried to use the criteria and AND logic to limit what is displayed. The query results are printed with the students name on it.	Creates a Query on their data and then uses the criteria and AND logic to limit what is displayed. The query results are printed with the students name on it.	Creates a Query on their data and then uses the criteria and AND logic to limit what is displayed. The query results are printed with the students name on it and an attempt to explain what the data represents.	Creates a Query on their data and then uses the criteria and AND logic to limit what is displayed. The query results are printed with the students name on it and an explanation of what the data represents.	Creates a Query on their data and then uses the criteria and AND logic to limit what is displayed. The query results are printed with the students name on it and an accurate explanation of what the data represents.
Creates a Query that uses OR logic	Creates a Query on their data and then tried to use the criteria and OR logic to limit what is displayed. The query results are printed with the students name on it.	Creates a Query on their data and then uses the criteria and OR logic to limit what is displayed. The query results are printed with the students name on it.	Creates a Query on their data and then uses the criteria and OR logic to limit what is displayed. The query results are printed with the students name on it and an attempt to explain what the data represents.	Creates a Query on their data and then uses the criteria and OR logic to limit what is displayed. The query results are printed with the students name on it and an explanation of what the data represents.	Creates a Query on their data and then uses the criteria and OR logic to limit what is displayed. The query results are printed with the students name on it and an accurate explanation of what the data represents.
Creates a query that uses a parameter	Student was able to create a parameter query did not ask for help and had issues. They query results are printed with the students name on it.	Student was able to create a parameter query with help that ran. They query results are printed with the students name on it.	Student was able to create a parameter query with help that ran correctly.They query results are printed with the students name on it and an attempt to explain what the data represents.	Student was able to create a parameter query with minor help that ran correctly. They query results are printed with the students name on it and an explanation of what the data represents.	Student was able to create a parameter query independently that ran correctly. The query results are printed with the students name on it and an accurate explanation of what the data represents.
Create a Top Value query	Student was able to create a top value query did not ask for help and had issues. They query results are printed with the students name on it.	Student was able to create a top value query with help that ran. They query results are printed with the students name on it.	Student was able to create a top value query with help that ran correctly.They query results are printed with the students name on it and an attempt to explain what the data represents.	Student was able to create a top value query with minor help that ran correctly. They query results are printed with the students name on it and an explanation of what the data represents.	Student was able to create a top value query independently that ran correctly. The query results are printed with the students name on it and an accurate explanation of what the data represents.
Create a Joined Table query	Student was able to create a Joined Table query did not ask for help and had issues. They query results are printed with the students name on it.	Student was able to create a Joined Table query with help that ran. They query results are printed with the students name on it.	Student was able to create a Joined Table query with help that ran correctly.They query results are printed with the students name on it and an attempt to explain what the data represents.	Student was able to create a Joined Table query with minor help that ran correctly. They query results are printed with the students name on it and an explanation of what the data represents.	Student was able to create a Joined Table query independently that ran correctly. The query results are printed with the students name on it and an accurate explanation of what the data represents.
Add a Calculation to the Joined Table Query	Student was able to create a Calculation did not ask for help but had issues. The query results are printed with the students name on it.	Student was able to create a Calculation with help that ran. The query results are printed with the students name on it.	Student was able to create a Calculation with help that ran correctly. The query results are printed with the students name on it and an attempt to explain what the data represents.	Student was able to create a Calculation with minor help that ran correctly. The query results are printed with the students name on it and an explanation of what the data represents.	Student was able to create a Calculation independently that ran correctly. The query results are printed with the students name on it and an accurate explanation of what the data represents.