CODEWITHSUNDEEP
ms-access
Jacques Tardie
Jacques Tardie

Reputation: 595

Copying a field to another table in Access

I'm a bit embarassed asking this here, but here goes:

I've got two tables, which you can see here:

http://img411.imageshack.us/img411/4562/query.jpg

I need to copy the effortid from the one table into the other, making sure that the values still maintain the correction relationships. The primary key for each is a combination of loggerid & datetime. What's the best way to do this?

Thanks in advance, and don't make fun :)

Upvotes: 0

Views: 6452

Answers (1)

Clownish Carnage
Clownish Carnage

Reputation: 109

Change it to an Update Query instead. The joins should function correctly, but will not add missing rows. To do that, you would use an Append Query, like you have setup, but with a left join and a check for nulls. The sample below updates the LogID table with information residing in LogSiteID table.

Append Missing Records from Logger Site ID to LogID

INSERT INTO logID ( [Datetime], loggerid, temp, effortid )
SELECT ls.datetime, ls.loggerid, ls.temp, ls.effortid
FROM logID AS l RIGHT JOIN [Logger Site ID] AS ls ON (l.temp = ls.temp) AND (l.loggerid = ls.loggerid) AND (l.Datetime = ls.datetime)
WHERE (((l.loggerid) Is Null));

Update effortids from Logger Site ID to LogID

UPDATE logID AS l INNER JOIN [Logger Site ID] AS ls ON (l.Datetime = ls.datetime) AND (l.temp = ls.temp) AND (l.loggerid = ls.loggerid) SET l.effortid = [ls].[effortid];

Upvotes: 1

Related Questions