• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 294
  • Last Modified:

Updating a Table in Access 2010

I have been tring to update a table in Access 2010 from another table. Table A has a list of all our PCs on the network. TableA is missing information that TableB has, specifically  LastLogonID, LastLogonDomain, and IPAddresses.

So here is the Code I came up with:
UPDATE TableA[All] AS A
INNER JOIN TableB AS B ON A.SystemName = B.SystemName
SET A.LastLogonID = B.LastLogonID, A.LastLogonDomain = B.LastLogonDomain, A.IPAddresses = B.IPAddresses;

Open in new window


It runs but nothing happens, can any help me out here. Am I trying to change to many fields?
0
Everwulf
Asked:
Everwulf
1 Solution
 
Rey Obrero (Capricorn1)Commented:
try this


UPDATE TableA AS A
INNER JOIN TableB AS B ON A.SystemName = B.SystemName
SET A.LastLogonID = B.LastLogonID, A.LastLogonDomain = B.LastLogonDomain, A.IPAddresses = B.IPAddresses;
0
 
EverwulfAuthor Commented:
Sorry What you have is what i am using not sure where the [All] came from.
0
 
Rey Obrero (Capricorn1)Commented:
your update query will only update records where A.SystemName = B.SystemName

if you want to add records from to table A from table B
use an append query

insert into TableA
select TableB.* from TableB
where TableB.SystemsName not in (select SystemsName From tableA)
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Eric FlammSenior ConsultantCommented:
Try
Update TableA
inner join TableB B on TableA.SystemName=B.SystemName
Set LastLoginID=B.LastLoginID, LastLogonDomain=B.LastLogonDomain,IPAddresses=B.IPAddresses

Open in new window

I think the Alias (A) is valid in the join (although I removed it for clarity), but not in the Update command.
0
 
EverwulfAuthor Commented:
I think I found my issue. Thanks to Capricorn1. For some reason out of the 185 records in TableB it only finds the first record as a match to TableA. Not exactly sure why it started doing that.

Now I have to figure out why it doesn't see the rest.
0
 
HndlWCareCommented:
Make sure both tables have all of the SystemName, it won't update if it can't match a similiar entry.

You should try to avoid using JOINs with Access also, a workaround would be :

UPDATE TableA AS A, TableB AS B
SET A.LastLogonID = B.LastLogonID, A.LastLogonDomain = B.LastLogonDomain, A.IPAddresses = B.IPAddresses
WHERE A.SystemName = B.SystemName;

Look closely at the WHERE clause, both tables NEEDS all of those SystemName
0
 
Rey Obrero (Capricorn1)Commented:
<You should try to avoid using JOINs with Access also, a workaround would be :>

and why is that?
0
 
EverwulfAuthor Commented:
Ok I got it figured out. Of the list in TableB only one machine is actually listed. Which means two things our inventory is out of date and I need to do more investigating before waisting everyones time.
0
 
EverwulfAuthor Commented:
Not all the same data was in the column I was comparing. Would not have stopped to think about that had Cap not stated that:

"your update query will only update records where A.SystemName = B.SystemName"

Thanks again Cap
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now