Trying to Understand Update Queries

[Don]

Let me set the stage then try to explain what I think I understand about
update. Basically, I am adding some data (a new table) to our employee
database from another, external data source. (I'm going to simplify for the
sake of clarity.) In the existing database, there is tblMaster which
contains the master core records for each employee. For this example, the
fields of consequence are EmployeeID, LastName, and FirstName.

The new table (after import), has among other fields, LastName and
FirstName, but not EmployeeID. (EmployeeID is what is used to link
tblMaster to all other tables.) So, my goal is to add the EmployeeID field
to the new table (tblNew) so the architecture is consistent.

So, I figured an UPDATE query was a good candidate to copy the value of the
EmployeeID field from tblMaster to tblNew based on a temporary link on
LastName and FirstName. However, it seems all the examples I have found for
update queries have the updated field take on some "static" value, not a
value dependent on the link between the tables. Does this mean one cannot
update a field in one table with data from another table based on a link?
Or is it simply my interpretation is not correct? If the latter, would
someone kindly explain how this would be done.

Thanks!

Don

 

[Michel Walsh]

Hi,


You can update values from another table, that is sure. Blame the standard
books examples.


UPDATE table1 INNER JOIN table2 ON table1.lookingFor = table2.matchingIt
SET table1.f1 = table2.g1


will update f1 (from table1) with g1 (from table2), looking for the record
in table2 such that matchingIt equal the value of lookingFor in the record
being updated in table1.


Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Hi,


Note that an easy way is to first make a standard SELECT query implying the
two tables and one the "updating" record match the record to be updated,
change the SELECT to an UPDATE (with the menu or the toolbar) and complete
the Update line with the propers tablename.fieldname (and remove fields not
to be updated from the grid).

Hoping it may help,
Vanderghast, Access MVP

 

[Chris2]

Let me set the stage then try to explain what I think I understand about
update. Basically, I am adding some data (a new table) to our employee
database from another, external data source. (I'm going to simplify for the
sake of clarity.) In the existing database, there is tblMaster which
contains the master core records for each employee. For this example, the
fields of consequence are EmployeeID, LastName, and FirstName.

The new table (after import), has among other fields, LastName and
FirstName, but not EmployeeID. (EmployeeID is what is used to link
tblMaster to all other tables.) So, my goal is to add the EmployeeID field
to the new table (tblNew) so the architecture is consistent.

So, I figured an UPDATE query was a good candidate to copy the value of the
EmployeeID field from tblMaster to tblNew based on a temporary link on
LastName and FirstName. However, it seems all the examples I have found for
update queries have the updated field take on some "static" value, not a
value dependent on the link between the tables.

Don,

What exact process do you mean by, "a value dependent on the link
between the tables?"

If the names are matching between the Employee table and the ImportNew
table, then aren't you just copying the EmployeeID that is already in
Employee? (And how you can you be sure there won't be two employees
with the same name?)

(If the names in ImportNew are not in Employee, and EmployeeID is not
an AUTOINCREMENT column, that you would want to generate new
EmployeeID's in ImportNew based on the MAX(EmployeeID) + 1 and up;
except that I would, personally, find it easier to just to APPEND new
rows in Employee with new ascending EmployeeID values.)

Does this mean one cannot
update a field in one table with data from another table based on a

link?

Yes, you can.

Or is it simply my interpretation is not correct? If the latter, would
someone kindly explain how this would be done.

I'll need to know exactly what it is you want done.

Thanks!

Don

Although meant for microsoft.pulic.sqlserver.programming, the
following
link is still applicable for microsoft.pulic.access.queries:
http://www.aspfaq.com/etiquette.asp?id=5006, and is excellent when it
comes to detailing how to provide the information that will best
enable
others to answer your questions.


Sincerely,

Chris O.

 

[Don]

Michael,

Worked through your example and it worked like a charm! Much simpler then
the examples were leading me to believe!

Thanks!

Don

 

[Don]

Chris,

Thanks for replying. Michael's post got me on my way.

Don

 

[Chris2]

Chris,

Thanks for replying. Michael's post got me on my way.

Don

Don,

Yes, it looks like I was reading too much into what you needed. Sorry
about that.


Sincerely,

Chris O.