Project 3B Overview | Sailor's Age | Query in Query | Structure Modifications | Export XML

next page

Using a query in a query

3 Catalyst questions on this page

Query1

Now we'll use querySailorAge in another query.

1. Create a new query as you've done before. Save it as query1.

2. Add tableSailors

2. Before closing the Show Table box, click on the Queries tab. You'll see querySailorAge listed:

3. Double click on querySailorAge to add it to your new query. Access will automatically show a relationship between them:

4. We want to see a list of sailors and their ages, so add the fields sailorName from tableSailors and current age from querySailorAge to the query:

5. Run the query to view the result:

 

6. To sort by age, you can do it either of two ways

a. add that criteria to the query,

or you can do it this way:

b. Click on the Home tab , click the current age column, and click the Ascending Sort button in the menu ribbon

The result looks like this:

If your result looks different, did you remember to change back to years from months in querySailorAge?

7. Now change your sort to Descending and save your query again. Run the query.

8. Go to the Catalyst quiz and answer questions 2 and 3.

 

Query 2

9. Save your query as query2.

10. Now go back into design view and modify your query so it looks like the image below. (Reminder: to get the Total row to appear, click on the totals icon which is available when you’re in the Design tab ):

11. Save again as query2.

12. View the results of this query. It shows you the ages of sailors in the database and the numbers of sailors who are that age.

13. Go to the Catalyst quiz and answer question 4.

Next, you'll look at more modifications that have been made to the database