Update Query to transfer data from one table to another.

[Jay]

I have a database which runs offline, no network
connection.
I need to udate a column with the current employee badge
number right before I take it offline.

So Local table has columns like:
Empl# Badge# info1 info2 info3 info4

The network table has:
Empl# Badge# LName FName

So I want to run and update query that will update all of
the Local table Badge#s based on the Empl# being equal.

Any help would greatly be appreciated.

Thanks

 

[MGFoster]

I have a database which runs offline, no network
connection.
I need to udate a column with the current employee badge
number right before I take it offline.

So Local table has columns like:
Empl# Badge# info1 info2 info3 info4

The network table has:
Empl# Badge# LName FName

So I want to run and update query that will update all of
the Local table Badge#s based on the Empl# being equal.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


UPDATE Local As L INNER JOIN Remote As R ON L.Empl#=R.Empl#
SET L.Badge# = R.Badge#

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQGOHsYechKqOuFEgEQK4tQCdFsIpbg2e8zS7RTadl8PNHxkGk+0AoO8m
Zk77+MaKcC1AUpTyWhJQXUYS
=s7/W
-----END PGP SIGNATURE-----

 

[John Vinson]

I have a database which runs offline, no network
connection.
I need to udate a column with the current employee badge
number right before I take it offline.

So Local table has columns like:
Empl# Badge# info1 info2 info3 info4

The network table has:
Empl# Badge# LName FName

So I want to run and update query that will update all of
the Local table Badge#s based on the Empl# being equal.

A simple JOIN will work here, if Empl# has a unique Index (it should!)

Create a query joining the two tables by Empl#. Change it to an Update
query; update Local.[Badge#] to

=[network table].[Badge#]

The brackets are required.

 

[Jay]

I guess I should've given my error, but I just thought
maybe I was doing it wrong...
When I put this in I get

"Operation Must Use An Updateable Query"

If I remove the join and do a simple update comment feild
to hi where empl# = 5 it works fine, so I assumed it
must've been the join that screwed me up.

Update Local INNER JOIN Remote ON Local.[Employee Number]
= Remote.EMPLOYNUM set Local.badge = remote.badgeid

After looking up the error I think my problem may be that
there are multiple rows in the Local table with the same
employee number.

Can an update be done on a many-to-one join?

 

[Jay]

I must be stewnad....
I didn't have any primary key on the local table, and
there was no unique identifier.....
I added an index column as autonum and it operates as one
would expect....
thanks

-----Original Message-----
I guess I should've given my error, but I just thought
maybe I was doing it wrong...
When I put this in I get

"Operation Must Use An Updateable Query"

If I remove the join and do a simple update comment feild
to hi where empl# = 5 it works fine, so I assumed it
must've been the join that screwed me up.

Update Local INNER JOIN Remote ON Local.[Employee Number]
= Remote.EMPLOYNUM set Local.badge = remote.badgeid

After looking up the error I think my problem may be that
there are multiple rows in the Local table with the same
employee number.

Can an update be done on a many-to-one join?

-----Original Message-----


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


UPDATE Local As L INNER JOIN Remote As R ON L.Empl#=R.Empl#
SET L.Badge# = R.Badge#

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQGOHsYechKqOuFEgEQK4tQCdFsIpbg2e8zS7RTadl8PNHxkGk

+

0AoO8m
Zk77+MaKcC1AUpTyWhJQXUYS
=s7/W
-----END PGP SIGNATURE-----

.

.