Queries

Queries are used for a variety of reasons. You can create a query from a table or another query. Queries can filter out specific records. Queries are also used to combine information from various tables or queries within a database. Queries help you perform calculations, group records, count records, sort records, and more.

Simple Query Wizard

To create a query you must first click on the Query tab in the database window. Then click on New and select Simple Query Wizard. A window pops up and you click on the Table/Queries drop down list and select a specific table or query to include in your new query. When you make your selection, the field names within that table or query are displayed in the Available Fields category. You can use the arrows to the right to select which of those fields you would like in your query. When you select a field it should be displayed in the Selected Fields list. Once you have selected all of the fields from the table or query, you can then click on the Table/Queries drop down list again and select another table or query and then follow the same steps as above to put those fields into your query. When you have selected all of the fields from all the tables you are interested in, click on the NEXT button.

The next screen asks you to type in a name for your query. You then can choose whether you would like to view the query in design or datasheet mode. After you make that choice click on FINISH.

Working with Queries

Sorting and Filtering

Open a query in design view to sort or filter the records. When you open a query in design view you should see a top portion of the screen with boxes representing each table or query with a list of all the fields contained in it. In the bottom portion you have a column for each field in your query. The rows beneath the field name provide you with options to filter and sort the records in that field. The first row tells you which table or query the field is from. The second row allows you to sort the records in that field. Click in the sort cell and then click on the drop down list to select ascending or descending.

When you filter records in Queries you are Setting Criteria. In the fifth row of the design sheet of queries you can type in a value to filter the records. For example, if I wanted to know all the people in my Address Database that had a 44109 zip code, I would type in 44109 in the zip code field in the criteria row. When I move my cursor, Access automatically puts quotation marks around the 44109. To see those records that have a 44109 zip code, I could click on datasheet view or click on the run toolbar button.

In case I wanted to find all the people who have a 44109 zip code or a 44123 zip code, I could type in 44123 underneath the 44109 in the row that says OR. I could then run the query to see all the people in my address database that had a 44109 or 44123 zip code.

Reports

When you are ready to print out a document, you can use reports to make quality print outs of your tables and queries.

Report Wizard

To make a report with the help of the Wizard, select the table or query you would like to print out and then choose Report Wizard from the New object toolbar button.
The first thing the Wizard will ask you is what fields from the table or query would you like to have inould like fields from different tables or queries, use the Tables/Queries drop-down list. After selecting a table or query, the fields that are in that object will appear underneath. After selecting the fields, the Wizard allows you to group your data. For example, addresses can be grouped by zip code so the print out will have a zip code as a heading with all of the data within that one zip code displayed underneath it. The next step asks if you want to sort the data by any field. The last few steps deal with formatting. The Wizard gives you a few formats to choose from, use the preview to see what each pattern looks like. Lastly, the Wizard asks you to name the report.

Auto Report

Using the Auto Report option generates a standard report by the computer. The user is not asked any questions. To use Auto Report, select a table or query, then choose Auto Report from the New object toolbar button.

Labels
If you would like to print out information onto labels (for example: addresses onto envelope labels or names on Hello My Name Is… labels) then use the labels wizard. First select on the table or form you would like to use and choose the Report Wizard. Next, choose Label Wizard and follow the instructions. What is nice about software today is it has templates for the AVERY labels (Avery is a company name that makes most labels). When you go to the store to buy labels, there is usually a four number code at the top of the box. This number is needed when you tell Access which label size you would like to print on (see picture). The other thing the Wizard will ask you is which fields do you want on your labels (see picture). What you do is click on the field name on the left, hit the button with an > symbol and the field moves to the right on your label. Remember to use spaces, commas, or ENTER between fields. The way you place your fields here is how they will look on your labels. Next the Wizard allows you to sort the labels by any of the fields. The last step it asks you is to name the report and hit FINISH.

Print

In Access it is very easy to print. Whenever you have table, queries, forms, or reports open, click on the print toolbar button to start printing. To take a peek at what the printed pages will look like use print preview. Note, when filtering or sorting a table or query you can press print and just the data that you see will be printed. Printing from tables and queries is pretty dry. To make your prints look profession, use reports.

Back to Access Syllabus