Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

اینجــــا یک کتابخانه دیجیتالی است

با بیش از 100000 منبع الکترونیکی رایگان به زبان فارسی ، عربی و انگلیسی

Access Cookbook, 2nd Edition [Electronic resources] - نسخه متنی

Ken Getz; Paul Litwin; Andy Baron

| نمايش فراداده ، افزودن یک نقد و بررسی
افزودن به کتابخانه شخصی
ارسال به دوستان
جستجو در متن کتاب
بیشتر
تنظیمات قلم

فونت

اندازه قلم

+ - پیش فرض

حالت نمایش

روز نیمروز شب
جستجو در لغت نامه
بیشتر
لیست موضوعات
توضیحات
افزودن یادداشت جدید










Recipe 1.6 Use a Field in One Table to Update a Field in Another Table



1.6.1 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
wayshort of using a complex VBA procedureto 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?


1.6.2 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:

  1. 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.

  2. Select Query Update to change the type of query to an
    update action query.

  3. 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


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.

  1. 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 any
    existing non-null values in tblAlbums.

  2. Execute the query by selecting Query Run or by clicking on
    the exclamation point icon.



You can preview the selected records in an action query by choosing
View Datasheet or by clicking on the Datasheet icon. The
query will not be run, but you'll be able to see
which records would be updated had you run the query.

For an example of updating a field in a
table based on the value of a field in another table, open the
tblAlbums table found in the

01-06.MDB database.
Note that most of the purchase prices are null (see Figure 1-15). Open tblAlbumsUpdated, and
you'll see that many of the purchase prices for the
same albums have been entered (see Figure 1-16).


Figure 1-15. Many of the purchase values in tblAlbums are null



Figure 1-16. tblAlbumsUpdated contains updated purchase prices for several albums in tblAlbums


Now run the qryUpdateAlbumPrices query found in the same database
(see Figure 1-17). This action query will take the
PurchasePrice values from tblAlbumsUpdated and copy it into the
Purchase Price field in tblAlbums for each record where the two
AlbumID fields match and the price value in tblAlbums is currently
null. When the query is finished, open tblAlbums againyou
should see that the Purchase Price field in this table has been
updated based on the values in tblAlbumsUpdated (see Figure 1-18).


Figure 1-17. The qryUpdateAlbumPrices update query in design view



Figure 1-18. The updated purchase prices for albums in tblAlbums



1.6.3 Discussion


You can use update queries in Access to update the values in a target
table, and you can use another table to supply the values for the
update. The trick is to join the two tables using a common field and
to properly specify the name of the field from the source table in
the Update To cell.

You can update more than one field at a
time in an update query. You can also include additional fields in
the query grid to further limit the rows to be updated. Drag these
additional fields to the query grid and specify criteria for them. As
long as you leave the Update To row blank for these columns, they
will be used for their criteria only and will not be updated. Update
queries are the most efficient way to make bulk changes to data; they
are much more efficient than using a recordset in a VBA procedure.


/ 232