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? Query Guidelines 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 File:AC2-Query_Design_View_Criteria.jpg 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. File:AC2-Wildcard_Query.jpg Then when you run the query you get the results: File:AC2-Wildcard_Query_Results.jpg 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. File:AC2-AND_Query.jpg 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. File:AC2-OR_Query.jpg 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. File:AC2-Parametery Query.jpg The program will then prompt the user to enter the Employee number when it is run and the following Pop-up will be displayed. File:AC2-Popup_Parameter_Query.jpg The user would type in an employee number such as EJ and click OK Access would only display the records for employee number EJ File:AC2-Parametery_Query_Results.jpg 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.https://support.office.com/en-us/article/make-summary-data-easier-to-read-by-using-a-crosstab-query-8465b89c-2ff2-4cc8-ba60-2cd8484667e8 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. File:AC2-Sort_from_Dropdown.jpg From the Design View you can choose different fields. The field to the left is the primary sort key. File:AC2-Sort_From_Design_view.jpg 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. Report of Query Project Project 2 Creating Queries: Create the following Queries and print the results for your business: Create a simple query on your table that contains the 10 records. Use the drop down to limit the data displayed Print the results Write on the paper what you did, in a full sentence so I can read and understand it. Use the Query you created for #1 Do an AND criteria query Print the results Write on the paper what you did, in a full sentence so I can read and understand it. Use the Query you created for #1 Do an OR criteria query Print the results Write on the paper what you did, in a full sentence so I can read and understand it. Create a parameter query Show the teacher that it works Create a Top Value Query from one of your tables Print the results Write on the paper what you did, in a full sentence so I can read and understand it. Use the Query you created for #1 Create a join table query that uses your sales/invoices/appointments table Pull in all data from the different tables to show what is needed for the document Add a calculation for the total Create a report from the query Print the report Staple all the items together Make sure you name is on it Rubric + Create Queries for BusinessStandards1 point2 points3 points4 points5 pointsCreate a Simple QueryCreates 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 parameterStudent 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 queryStudent 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 queryStudent 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 QueryStudent 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.