Update Query updating only some records

[Margarita]

Hello,
I am working in Access 2003. I am trying to run a simple update query: update employee ID based on last name and first name. The problem is that the query updates 7 records out of over 300, leaving the rest null. I can't figure out why that is. All the first and last names come from the same excel spreadsheet which I imported to create the table and all of them are formatted the same. The sql is below. Can anyone shed some light on why an update query could behave this way? Thank you!

UPDATE EmployeeTask LEFT JOIN EMPLOYEE_TEMP ON (EmployeeTask.LastName=EMPLOYEE_TEMP.LastName) AND (EmployeeTask.FirstName=EMPLOYEE_TEMP.FirstName) SET EmployeeTask.EMPLID = EMPLOYEE_TEMP.EMPLID;

 

[jdraw]

Why the Left Join?

 

[MSAccessRookie]

Hello,
I am working in Access 2003. I am trying to run a simple update query: update employee ID based on last name and first name. The problem is that the query updates 7 records out of over 300, leaving the rest null. I can't figure out why that is. All the first and last names come from the same excel spreadsheet which I imported to create the table and all of them are formatted the same. The sql is below. Can anyone shed some light on why an update query could behave this way? Thank you!

UPDATE EmployeeTask LEFT JOIN EMPLOYEE_TEMP ON (EmployeeTask.LastName=EMPLOYEE_TEMP.LastName) AND (EmployeeTask.FirstName=EMPLOYEE_TEMP.FirstName) SET EmployeeTask.EMPLID = EMPLOYEE_TEMP.EMPLID;

I assumed that the LEFT JOIN was due to the fact that the EmployeeTask Table could contain records that were not in the EMPLOYEE_TEMP Table.

While I can only guess as to the cause of the problem you are having, if you see how many records you get with the query below, and take a look at the results, the answer could be in there.

Select Task.EMPlID, Temp.EMPLID. Task.FirstName. Temp.FirstName, Task.LastName, Temp.LastName

 

[Margarita]

I just tried it with an inner join, and it doesn't solve my problem- it updates a slightly higher number of records (21) but still not all of them. Any ideas where I could be going wrong?
Thanks!

 

[Margarita]

Hello, sorry- I am missing the from clause of your query.
Thanks.

 

[jdraw]

How many records in EmployeeTask have an empty FirstName and or empty lastName?

SELECT EmployeeTask.Lastname
, Count(EmployeeTask.Lastname) AS CountOfLastname
, EmployeeTask.firstName
, Count(EmployeeTask.firstName) AS CountOffirstName
FROM EmployeeTask
GROUP BY EmployeeTask.Lastname, EmployeeTask.firstName;

 

[Margarita]

There are no blanks in LastName or FirstName- I am using LastName, FirstName and TaskSubcategory as the primary key (several tasks for each employee). So the update query that I tried is strange in two ways:

1- when I try the left join (because there are some people in the Task table that are not in the Employee_TEMP table- the rest are in the EMPLOYEE_REG table), the query message says that it is about to update all 357 records, but only updates 7 (though there are more than 7 temp employees in the Task table).

2- when I try the inner join, the query says it will update 21 records and updates 21- though that is still not everyone who should be updated.

I just tried the query that you sent me and it returned 210 records- the exact number of employees in the Task table. So still not sure why the update isn't working...
Thanks!

 

[MSAccessRookie]

Hello, sorry- I am missing the from clause of your query.
Thanks.

That's because I left it on the notepad by mistake, sorry.

FROM EmployeeTask LEFT JOIN EMPLOYEE_TEMP


(ALSO TRY RIGHT JOIN and INNER JOIN)

 

[MSAccessRookie]

There are no blanks in LastName or FirstName- I am using LastName, FirstName and TaskSubcategory as the primary key (several tasks for each employee). So the update query that I tried is strange in two ways:

1- when I try the left join (because there are some people in the Task table that are not in the Employee_TEMP table- the rest are in the EMPLOYEE_REG table), the query message says that it is about to update all 357 records, but only updates 7 (though there are more than 7 temp employees in the Task table).

2- when I try the inner join, the query says it will update 21 records and updates 21- though that is still not everyone who should be updated.

I just tried the query that you sent me and it returned 210 records- the exact number of employees in the Task table. So still not sure why the update isn't working...
Thanks!

My purpose for asking you to SELECT as opposed to UPDATE was so that you would see what would be updated. I suspect that you will not see what you are expecting and what you see might lead you to an answer.

 

[Margarita]

Hi AccessRookie, I tried the select statement with an inner join and the it's not giving me the expected results: it shows only 21 people as non-nulls in the Employee_Temp Last and First name fields. There are over 40 people who I KNOW are both in the Employee_Temp AND the EmployeeTask tables who are coming up with blanks for EmployeeTemp.LastName and EmployeeTemp.FirstName fields. Why are the tables not joining as I expect? Thank you.

 

[MSAccessRookie]

Hi AccessRookie, I tried the select statement with an inner join and the it's not giving me the expected results: it shows only 21 people as non-nulls in the Employee_Temp Last and First name fields. There are over 40 people who I KNOW are both in the Employee_Temp AND the EmployeeTask tables who are coming up with blanks for EmployeeTemp.LastName and EmployeeTemp.FirstName fields. Why are the tables not joining as I expect? Thank you.

That's hard to say without seeing the data, but you can check the following and see if any of them are involved:

1. Are there Null Values in FirstName or LastName in either Table?
2. Are all of the FirstNames and LastNames spelled EXACTLY the same?
   1. Case Sensitivity
   2. Leading and/or trailing spaces or tabs

If none of the above applies, then we will need to consider additional options.

 

[Margarita]

Thank you for following up! To answer your questions:

1. There are no nulls in first or last names.
2. The spelling is identical. All the names in Employee_Temp are lower case and in the EmployeeTask they are a mix of lower case and upper case. However, the records that were successfully joined are a mix of lower-to-lower and upper-to-lower.
There shouldn't be any trailing spaces- I think- as I applied the trim() function in excel before importing it to the database.

I should probably mention that this problem arose as another issue in a long string of problems that I have been having with Access- not just with this database but several others as well. A bizarre issue that I had to deal with and none of the IT folks could help me with was instances of sql getting erased- not syntax-edited, but ERASED in random parts of queries. I have learned to deal with this by running a Repair Office Installation every once in a while, which seems to help, but this latest problem doesn't seem to lend itself to a quick fix. Do these sound like signs of database corruption or a larger issue with the application itself?

 

[jdraw]

Did you try running the query I suggested in post #6?

You may have some sort of corruption based on your comment re Erasing parts of sql....??

 

[Margarita]

Hi jdraw, yes- I ran your query and it returned the expected counts and the correct number of records in the Task table- 210.

I agree that I am having corruption issues, though no one seems to be able to figure out how to fix it...