Access Cookbook - Ken Getz [18]
If the count is exactly 1, the query uses the first and last names:
[FirstName] & " " & [LastName]
The immediate If function, IIf, does this for you, as shown in Step 5. It looks at the value in the [Residents] field and decides which format to use based on that value.
Access does its best to optimize nested queries, so don't feel shy about resorting to basing one query on another. In this case, it simplifies the work. The first-level query groups the rows, and the second one creates the calculated expression based on the first. Though it might be possible to accomplish this task in a single query, splitting the tasks makes it easier to conceptualize.
We also could have solved this problem by changing the design of the database so that instead of having a single table, tblNames, with repeating address information for multiple family members, we had two tables, perhaps called tblFamilies and tblFamilyMembers, related in a one-to-many relationship.
See Also
To include quotes inside quoted strings, see Recipe 7.1 in Chapter 7.
1.6. Use a Field in One Table to Update a Field in Another Table
Problem
You've imported a table that contains updated prices for some of the records in a table in your database. The data in all the other fields in the existing table is still correct. Is there any way—short of using a complex VBA procedure—to update the price data in the existing table based on the updated prices from the imported table without overwriting any of the other fields in the existing table?
Solution
You probably already know that you can use an Update query to update the values of fields in a table, but did you know that you can use an Update query to update the values in one table with the values from another? This solution will show you how to do just that. If you can join the two tables on some common field or combination of fields, you can use an Update query to update a field in one table based on the values found in a second table.
Here are the steps to create an Update query that updates values across tables:
Create a standard Select query. Add the two tables to the query and join them on the common field or fields. In the sample database, we added the tblAlbums and tblAlbumsUpdated tables to the query. We will refer to tblAlbumsUpdated as the source table because it will supply the values to be used to update the other table; tblAlbums is the target table because it will be the target of the updates. Access has automatically joined the two tables on AlbumID. If the name of the common field is not the same, you will have to join the two tables by dragging the common field from one table to the other.
Select Query → Update to change the type of query to an update action query.
Drag the field to be updated in the target table to the query grid. In the Update To cell for the field that will be updated, specify the fully qualified name of the field in the source table that will be the source of the updated values. This field name should include the name of the table surrounded by square brackets, a period, and the name of the field surrounded by square brackets. For qryUpdateAlbumPrices, drag the PurchasePrice field from tblAlbums to the query grid. The field settings for PurchasePrice are shown in Table 1-4.
Table 1-4. Field settings for qryUpdateAlbumPrices
Field
Table
Update To
Criteria
PurchasePrice
tblAlbums
[tblAlbumsUpdated].[PurchasePrice]
Is Null
WARNING
Be careful when specifying the Update To value. If you misspell the source field name, you run the risk of changing the values to the misspelled string rather than to the values in the source field. If Access surrounds the Update To value with quotes or prompts you for an unexpected parameter when you attempt to execute the update query, it's likely that you made a spelling mistake.
Optionally specify criteria to limit the rows to be updated. In the qryUpdateAlbumPrices example, we used criteria to limit the updated rows to those with null (missing) prices (see Table 1-4). This prevents Access from overwriting