update query

[Christina]

I need some help please...if possible just what I need to put in the design
view of the query, as I am not familiar with SQL codes.

I want the query to look match the names and amounts on Table 1 to Table 2,
and where the Names and Amounts are equal
to update Table 2 with the Voucher numbers in Table 1

Table 1 Table 2
Name Name
Voucher No Voucher No
Amount Amount
156 records 1056 records

Grateful for your help

Cristina

 

[John Spencer]

-- Add both tables to the query
-- Drag from Name to Name to establish a join
-- Drag from Amount to Amount to establish a compound join
-- Select Table 2 Voucher No as a field
-- Select Query: Update from the menu
-- Enter the following in the Update To cell under Table 2. Voucher No
[Table 1].[Voucher No]
-- Select Query: Run from the menu

The SQL would look like
UPDATE [Table 1] INNER JOIN [Table 2]
ON [Table 1].[Amount] = [Table 2].[Amount]
And [Table 1].[Name] = [Table 2].[Name]
SET [Table 2].[Voucher No] = [Table 1].[Voucher No]

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Here it is in SQL - enter it into the SQL view and then switch over to
the grid design view.

UPDATE [Table 2] INNER JOIN [Table 1] ON [Table 2].Name = [Table 1].Name
AND [Table 2].Amount = [Table 1].Amount
SET [Table 2].[Voucher No] = [Table 1].[Voucher No]
WHERE [Table 2].[Voucher No] IS NOT NULL

I added the WHERE clause so you wouldn't be updating any rows that don't
have a Voucher Number.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBR5CgaYechKqOuFEgEQLcrgCfRvo3IaI+RbE8v7hs8U67pvdGv1wAoNzy
PiGTEv1uQH7m5kt6EcL6cZfS
=ojfe
-----END PGP SIGNATURE-----