0

I have two tables and would like tbl2 to update tbl1.

tbl1 lives on a back end database on our network drive. It contains project information, regularly updated by a half dozen users.

tbl2 lives on a remote users laptop. It is a copy of tbl1 which he updates throughout the day. When he tries to access tbl1 over the VPN it is unusably slow.

The information on the remote users laptop is not terribly time sensitive. So I'm going to have him just export his table to the network drive at the end of each day.

I've trained the user to import tbl1 each morning and export tbl2 to the network drive each night.

So the coding part comes in at the end of the day. I need to pull his updates from tbl2 into tbl1.

I'm trying to accomplish this with an SQL query using this post as my guide. But I keep getting weird results.

SELECT tbl1.thing1, tbl2.thing1, tbl1.thing2, tbl2.thing2, tbl1.thing3, tbl2.thing3, tbl1.thing4, tbl2.thing4, tbl1.thing5, tbl2.thing5
FROM tbl1
FULL OUTER JOIN tbl2
ON (tbl1.thing1= tbl2.thing1) 
AND ( tbl1.thing2 = tbl2.thing2)
AND (tbl1.thing3 = tbl2.thing3) 
AND (tbl1.thing4 = tbl2.thing4)
AND (tbl1.thing5 = tbl2.thing5)
;

Is this possible? Am I going at it the wrong way?

After some further research this looks closer to right, but still not functional.

UPDATE [tbl1] INNER JOIN [tbl2]
ON (tbl1.Thing1=tble2.thing1),
(tbl1.thing2 = tbl2.thing2),
(tbl1.thing3 = tbl2.thing3),
(tbl1.thing4 = tbl2.thing4),
(tbl1.thing5 = tbl2.thing5)
SET AND ( tbl1.thing2 = tbl2.thing2)
AND (tbl1.thing3 = tbl2.thing3) 
AND (tbl1.thing4 = tbl2.thing4)
AND (tbl1.thing5 = tbl2.thing5);

Any other thoughts? Thank you!

Community
  • 1
  • 1
msiudut
  • 251
  • 1
  • 20
  • On that one I am. But anything I've tried so far I get some result that isn't tbl1 with tbl2's data written into it. Am I on the right track at least? – msiudut Jun 04 '15 at 18:11
  • The six users here locally will be able to view the project data and managers will be able to run reports against it. – msiudut Jun 04 '15 at 18:17
  • tbl1 is on a backend database on the network drive. Those six users interact with a front end ACCDE version of the database which is linked to tbl1. I want to dump the changes that the remote user makes to tbl2 during the day from tbl2 into tbl1. – msiudut Jun 04 '15 at 18:31
  • After some research, [this](http://stackoverflow.com/questions/12737221/update-msaccess-table-from-another-access-table-using-sql) looks like a better solution than a SELECT query. Sorry for any confusion, noob here. – msiudut Jun 04 '15 at 18:43

1 Answers1

0

It looks like my problem was solved indirectly in a different one of my questions. Thank you, Beth.

UPDATE tbl1 
INNER JOIN  tbl1 
ON Tbl1.ID = Tbl2.ID SET Tbl2.[thing1] = [Tbl1].[thing1], Tbl2.[ thing2] = [Tbl1].[thing2], Tbl2.[thing3] = [Tbl1].[thing3], Tbl2.[thing4] = [Tbl1].[ thing4], Tbl2.[ thing5] = [Tbl1].[thing5];
Community
  • 1
  • 1
msiudut
  • 251
  • 1
  • 20