Update query from 2 tables

[geospatial]

Access 2013
Windows 10

I have 2 tables (Prsnl, dataImport)

In Prsnl I have 2 fields (fullName,EDIPI)
In dataImport I have 2 fields(Name,EDIPI)

The EDIPI field in Prsnl is blank, and I want to fill it with the values from dataImport.EDIPI

The query I tried was

UPDATE Prsnl
SET Prsnl.EDIPI = dataImport.EDIPI
WHERE Prsnl.fullName = dataImport.Name

When I try and run it I get it asking to update 0 rows.

I also try and do a SELECT as follows

SELECT * FROM Prsnl
WHERE Prsnl.fullName = dataImport.Name

I get 0 results on that as well, but when I compare the 2 it looks the same. Prsnl.fullName is a calculated field (not sure if that would cause the issue and if so, How should I go about fixing that)

 

[stumac]

A couple of points:

Generally calculated fields are a bad idea - unnecessary and can cause problems, however can be joined on so don't think this is the issue, although depends what the calculation is... I assume it is joining the forename to the surname?!

The word 'Name' is a reserved word and shouldn't be used as a field name, in-fact access stops you using this - is this a linked table?

My guess is that one of the tables includes extra spaces, you can check this by using Trim in your join:

SELECT Prsnl.*
FROM DataImport INNER JOIN Prsnl ON Trim(DataImport.[Name]) = Trim(Prsnl.FullName)

The other thing it could be is that spaces in one of the tables might not be actual spaces... in which case you would need to find out what character it is and remove/replace them.

 

[stumac]

Actually - It could just have been the construct of your query was wrong... your tables are not joined, does this work:

SELECT Prsnl.*
FROM DataImport INNER JOIN Prsnl ON DataImport.[Name] = Prsnl.FullName

 

[geospatial]

Thanks,

That worked. Still learning the JOIN operations.