Stuck on UPDATE query

  • Thread starter Thread starter sparkle
  • Start date Start date
S

sparkle

Hi- I'm very stuck with an update query I'm attempting to do. I'm ne
and don't know all the possibilities or even if this is the route
should take. I have to get this project done in a few days and nee
some direction.

I have two tables, one called Main and one called Keys. I'm listing th
columns below;

Main- has 646 rows
LastName
FirstName
KeyNum

Keys- has 943 rows
EmpNum
LastName
KeyCombo


I want to get the KeyNum field data from the Main table to go into th
KeyCombo field in the Keys table, matching an employee last name t
their KeyNum

I'm stuck on where to join the two tables. I would think I could us
LastName with an innerjoin, but I only get 19 matches There are mor
than 500 matching last names, I'm sure. Is this because I have mor
rows in one table than the other?


I have also tried paring down the tables, renaming the columns an
doing a union query, with no luck.

Please give any suggestions as to what I should try next. Your help i
greatly appreciated
 
OK, can you copy and post the SQL of your query even though it does not give you
the expected results?

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

This might give us a hint.

I suspect that your Main.LastName is NOT matching up with Keys.LastName. Are
these two tables in ACCESS or are you linking to an Excel sheet or some other
data file?

Try doing just a SELECT query and see how many records are returned. This would
be the number of records that match between the two tables.

SELECT Main.*
FROM Main INNER JOIN Keys
ON Main.LastName = Keys.LastName


Also, I suspect that LastName as a matching criteria is going to cause you
problems, since Last Names are often duplicated in a company.

Try the following and see how many unique last names are in the Keys table. You
might find out that there are only a few.

SELECT DISTINCT Keys.LastName
FROM Keys

Normally the UPDATE query would look something like

UPDATE Keys INNER JOIN Main
ON Keys.LastName = Main.LastName
SET Keys.KeyCombo = Main.KeyNum

You might try the following and see if it makes any difference. If it does then
you have space characters in the Lastname field of at least one of the tables.

UPDATE Keys INNER JOIN Main
 
Back
Top