Introduction | Create Database | Tables & Keys | Data Types | Link Tables | Basic Query | More Queries

Querying the database - continued

Now you can create a query (a specification of what data you want to see) by choosing which tables and which fields to display.

On this page you'll learn to

• Add criteria
• Sort results
• Count results

 

When you view your results, the display you get is called the Datasheet view:

1. To go back to the display which allows you to modify your query, click on the little arrow below the Design icon and click on the Design View icon:

 

Adding Criteria:

2. To modify your query to show only the records that have "Aventura" in the Performer field, add the word "Aventura" to the Criteria row, like this:

3. Run the query. You should only see one record.

 

4. Go back to the Design view, delete the criteria "Aventura" from the Performer field and add the criteria "May" to the PerformanceDate field.

If you run this query, you will get zero results because the search is looking for fields that contain only the word "May" and nothing else.

9. To see records that include the word "May", but also include other text, type Like "*May*" :

10. Run this query. You should see 2 records. (Be sure this makes sense to you before continuing)

 

Sort your results:

11. Go back to the Design view. Notice the Sort row. If you click in it, you'll see a drop-down arrow:

If you click on the drop-down arrow, you'll see the choices for how to sort the results:

12. Create and run a query that sorts the results alphabetically, A-Z (Ascending) by Performer and then change it to show them in reverse alphabetical order (Descending) by Venue

 

Count your results:

Imagine that your list of concert events contains 1,000 records and you want to find out how many are in each venue. To do this, you would want to group your events by venue, then count the number in each group.

13. To do this with your database,

a. Close the old query.

b. Create a display that only shows the Venue field

14. Next, click on the Totals icon which is at the top of the page when you've clicked on the Design tab. A new row, Total, will appear. It will automatically contain the words Group By

15. Add another Venue field. (ID is always a good choice.) Click on Group By (a drop-down arrow will appear), click on the drop-down arrow and choose Count:

16. Run this query. You will see a list of venues (just two in this case), and the number of events at each (really what it's doing is grouping all of the records that are identical in every field that is included in the query, and then counting how many are in each group).

Check your results. This query will only give you the correcr results if your relationships are set properly. tableEvents has three records. If you add up the results in the count column, they should total 3. If not, make sure that your relationships between tables are properly set, as shown in this screenshot:

 

17. Save your query as query1, then close it.

 

Now that you've created several queries, we'll pause and review how to create a query from scratch...

18. Create a new query (click on the Create tab and then click on the Query Design icon).

19. The popup box will appear to allow you to add the tables you want to include in the query (include all 3).

20. Go to the Catalyst quiz and answer questions 9 - 14 based on the screen shots below (it will probably be easiest to create these queries in Access rather than just trying to figure them out in your head)

Query #1:

 

Query #2:

 

Query #3:

 

Query #4:

 

Query #5:

 

Query #6:

 

Display A:

 

Display B:

 

Display C:

 

Display D:

 

Display E: