Time for a quick Access quiz. These are the kinds of relational-query questions that separate "I can build a query" from "I know why the query is doing that weird thing." Keep score if you like, and try to answer each question before reading the explanation.
There are five questions covering foreign keys, joins, related records, and one of Access's most common little annoyances: the dreaded Enter Parameter Value box. Ready? Let's see how you do.
Question 1: What is the usual purpose of a foreign key field in an Access table?
Is it used to identify every record in that same table, store a reference to a related record in another table, automatically calculate totals, or prevent users from editing the table?
The answer is to store a reference to a related record in another table. A foreign key is how tables are connected in a relational database. For example, a CustomerID field in an Orders table is a foreign key because it points to the customer associated with each order.
The CustomerID in the Customers table is normally the primary key. The matching CustomerID in the Orders table is the foreign key. Same type of value, different job. One identifies the customer record itself; the other says, "this order belongs to that customer."
Question 2: What does an inner join in an Access query normally return?
Does it return every record from both tables, matched or unmatched? Only records with matching values in both tables? Every record from the first table only? Or only records with blank join fields?
The correct answer is only records that have matching values in both joined tables. That is exactly what an inner join does. If you join Customers to Orders using CustomerID, you will see customers who have orders and orders that belong to valid customers. Customers with no orders will not appear in that query.
This is often perfectly fine. If you are making an order report, you probably do not need to see every customer who has never bought anything. But it catches people off guard when they expect all customers and only get the ones with related records.
Question 3: You want to see every customer, including customers who have never placed an order. Which join should you use?
The answer is a left join that includes all customer records. In Query Design, this is the join option that says to include all records from Customers and only matching records from Orders. Customers without orders will still appear, but their order-related fields will be blank. That is not Access being broken. It is Access accurately telling you that no matching order exists.
Left joins are especially useful for finding customers who have never ordered, products that have never sold, employees with no assigned tasks, or any other "show me the records with no matching records over there" situation.
Question 4: When a query displays customer fields beside order fields, what happens to the underlying table data?
The correct answer is Access displays related data together without duplicating it in the tables. A query does not magically copy the customer's name and address into every order record. It simply follows the relationship while displaying the results.
This is one of the big benefits of relational databases. Store the customer information once in the Customers table. Store order information once in the Orders table. Use the CustomerID relationship when you need to see them together. No copying data around, no updating seventeen duplicate addresses because someone moved across town, no database goblins multiplying your records in the night.
Question 5: Why might an Access query unexpectedly display an Enter Parameter Value box for what appears to be a field name?
The answer is Access cannot find a field or control name referenced by the query. Most of the time, it is a typo. If your query asks for a field called LastNmae instead of LastName, Access does not know that you meant LastName. It assumes LastNmae might be a parameter you want the user to enter, so up pops that box.
The same thing can happen if you renamed or deleted a field, referenced a control on a form that does not exist, or misspelled a table name, query name, or form control reference. The parameter box is not necessarily asking for a legitimate parameter. Very often it is Access politely saying, "I have no idea what this name is supposed to mean."
When that box appears unexpectedly, do not just type something in and hope for the best. Check the spelling of field names in the query design grid, examine calculated fields and criteria, and verify any form references. If you are using something like Forms!MyForm!MyControl, make sure both the form and control names are correct and that the form is open when the query runs.
How did you do? If you got all five, congratulations, you are well on your way to becoming the chancellor of the high council. If any of these tripped you up, spend a little time working with relational queries, primary keys, foreign keys, and join types. Those concepts make a whole lot of Access behavior suddenly make sense.
You can watch the embedded video for the full quiz walkthrough and quick explanations.
Live long and prosper,
RR
No comments:
Post a Comment