Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / February 2008

Tip: Looking for answers? Try searching our database.

Update query help

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Floyd Forbes - 06 Feb 2008 03:27 GMT
I have Two tables with the same information. Table Two has a column called
Errors with data.
How can I update table one Error column with the same info in table two
errror column.

            table 1
table 2
nameid  name     date        items    errors                    nameid
name    date     items   errors
 100      Floyd    2/5/08    2500                                   100
Floyd   2/5/08  2500     4
  200     Anna     2/5/08     6000                                  200
Anna    2/5/08  6000     7
John Spencer - 06 Feb 2008 13:11 GMT
Use an update query.  Perhaps that would look like the following.
STEP 1:  BACKUP your data before attempting  the following.
STEP 2:  BACKUP your data before attempting  the following.

Without a backup you cannot restore the data if this does not work the way
you expect.

UPDATE Table1 INNER JOIN Table2
ON Table1.NameID = Table2.NameID
AND Table1.Name = Table2.Name
AND Table1.Date = Table2.Date
AND Table1.Items = Table2.Items
SET Table1.Errors = Table2.Errors

In the query grid (Design view)
-- add both tables
-- drag from each set of matching fields
-- add Table1.Errors to the fields
-- Select Query: Update query from the menu
-- TYPE the following in the update to "cell" under Table1.Errors
   [Table2].[Errors]
-- Select Query: RUN from the menu

If the two tables are identical in structure and contents then why do you
have two tables?  I am guessing that table2 is a subset of the records in
table1 and you only need table2 on a temporary basis to update table1.

Signature

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

>I have Two tables with the same information. Table Two has a column called
>Errors with data.
[quoted text clipped - 8 lines]
>   200     Anna     2/5/08     6000                                  200
> Anna    2/5/08  6000     7
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.