Think you know your way around a basic Microsoft Access select query? This quick quiz covers five important query concepts that beginners need to get right, especially the part where you edit data in a query and accidentally discover that, yes, you were editing the real table data. No undo button from the moon base required, but it helps to know what is happening.
Give yourself a point for each correct answer. Try answering each question before reading the explanation. If you get all five, congratulations: you are ready to keep the Luna City database running smoothly. If not, no worries. That is why we practice.
Question 1: What is the purpose of a select query in Microsoft Access?
A. To display selected data from one or more tables
B. To permanently copy records into a new table
C. To print a formatted report
D. To change every record in a table automatically
Answer: A. To display selected data from one or more tables.
A select query lets you choose the fields and records you want to see from one or more tables. You can filter records, sort them, combine related information, and display only the columns that matter for a particular task. It is not normally a separate copy of your data. It is more like a custom window into the data already stored in your tables.
Question 2: You need to filter customers by CreditLimit, but you do not want users to see the actual credit-limit amounts. What should you do?
A. Remove CreditLimit from the query after entering the criteria
B. Include CreditLimit in the query, enter criteria, and clear its Show box
C. Put CreditLimit only in the table's primary key
D. Convert CreditLimit to a Short Text field
Answer: B. Include CreditLimit in the query, enter criteria, and clear its Show box.
This is one of the handy little tricks in the query design grid. A field does not have to appear in the results just because you need it for filtering. Add CreditLimit to the grid, enter your criteria in the Criteria row, and then uncheck the Show box for that field. Access will use the field to decide which records belong in the result, but users will not see that column.
For example, you could display customers whose credit limit is over a certain amount without putting everyone's credit limit right out there for the office gossip committee to review.
Question 3: In a query design grid, you sort LastName ascending and FirstName ascending. Which field must be farther left to sort by last name first?
A. FirstName, because Access reads sorts from right to left
B. Either field, because Access sorts alphabetically by field name
C. LastName, because query sort priority runs from left to right
D. The field with the shorter values
Answer: C. LastName, because query sort priority runs from left to right.
In the Access query design grid, the leftmost sorted field has the highest priority. Put LastName to the left of FirstName, and Access will first arrange everybody by last name. Then, when several people share the same last name, it will sort those people by first name.
If you reverse the field order, you get a first-name sort with last name used only as the tie-breaker. That may not sound like a big deal until you are looking for Smith, John and Access has decided John comes before everybody else named anything.
Question 4: What normally happens if you edit an editable value while viewing a select query?
A. The change affects only the query's temporary results
B. Access creates a new record in the source table
C. The query becomes permanently read-only
D. The change is saved to the underlying table record
Answer: D. The change is saved to the underlying table record.
This is the big one. A select query is usually a live view of the data in its source table or tables. If the query is editable and you change a value in Datasheet View, you are changing the actual underlying record.
The query result is not a harmless temporary spreadsheet. It is real data. If you change a customer's phone number, status, address, or credit limit in an editable query, that change is saved back to the table record. So be careful when editing query results, especially when you are working with customer, financial, inventory, or other important data.
Not every query is editable. Some queries become read-only because of joins, aggregate calculations, grouping, unions, or other design choices. But when Access lets you type into a query result, assume you are modifying live data unless you have a very good reason to believe otherwise.
Question 5: A query is already open when another user changes a record so that it should now match the query criteria. You do not see it yet. What should you do?
A. Rebuild the query in Design View
B. Refresh the query results, or close and reopen the query
C. Save the query under a new name
D. Add a new primary key to the source table
Answer: B. Refresh the query results, or close and reopen the query.
An open query does not always immediately redisplay changes made by someone else. If another user updates a record and it should now appear in your results, refresh the query with F5, use the Refresh command, or close and reopen the query.
This is particularly important in a multi-user database. The underlying data may have changed, but the rows currently displayed in your open query may not update until Access reloads them. Good old F5 can save you from thinking your query is broken when it is really just showing an older view of the data.
How did you do? If you got five out of five, you have earned at least an honorary lunar database badge. If you missed a couple, that is perfectly normal. Queries are one of the most useful parts of Access, and understanding how filtering, sorting, live edits, and refreshing work will save you plenty of headaches later.
Watch the embedded video for the quiz format and explanations, and if you want a more complete beginner-level lesson on customer queries, filtering, sorting, and working with query results, check out Microsoft Access Beginner Level 1, Lesson 9.
Live long and prosper,
RR
No comments:
Post a Comment