update query

[Dan]

I have two tables. One is a linked table that is updated from an outside
program. The other is used inside access. The fields of Table 1 is as
follows: NSN, Part No, Noun, Type AC, Qty On Hand. The fields of Table 2
(the linked table) are NSN, Part-No,Qty-on-hnd.

How can I update table 1 with the updated quantities of table 2? I am new
to the query process so this is probably pretty simple but I don't have the
experience to get it to work.

 

[KARL DEWEY]

This query joins NSN and part number so as to update the correct item.
UPDATE Stock1 INNER JOIN Stock2 ON (Stock1.[Part No] = Stock2.[Part-No]) AND
(Stock1.NSN = Stock2.NSN) SET Stock1.[Qty On Hand] = [Qty-On-Hand];

You will also need to append any records that add a new part number for a
given NSN.
SELECT Stock2.NSN, Stock2.[Part-No], Stock2.[Qty-On-Hand], Stock1.NSN,
Stock1.[Part No]
FROM Stock2 LEFT JOIN Stock1 ON (Stock2.[Part-No] = Stock1.[Part No]) AND
(Stock2.NSN = Stock1.NSN)
WHERE (((Stock1.NSN) Is Null) AND ((Stock1.[Part No]) Is Null));

You will also need to list the new item add as they will not have Noun or
Type AC.
SELECT Stock1.NSN, Stock1.[Part No], Stock1.Noun, Stock1.[Type AC],
Stock1.[Qty On Hand]
FROM Stock1
WHERE (((Stock1.Noun) Is Null));

 

[ammodan via AccessMonster.com]

Thank you very much Karl

This query joins NSN and part number so as to update the correct item.
UPDATE Stock1 INNER JOIN Stock2 ON (Stock1.[Part No] = Stock2.[Part-No]) AND
(Stock1.NSN = Stock2.NSN) SET Stock1.[Qty On Hand] = [Qty-On-Hand];

You will also need to append any records that add a new part number for a
given NSN.
SELECT Stock2.NSN, Stock2.[Part-No], Stock2.[Qty-On-Hand], Stock1.NSN,
Stock1.[Part No]
FROM Stock2 LEFT JOIN Stock1 ON (Stock2.[Part-No] = Stock1.[Part No]) AND
(Stock2.NSN = Stock1.NSN)
WHERE (((Stock1.NSN) Is Null) AND ((Stock1.[Part No]) Is Null));

You will also need to list the new item add as they will not have Noun or
Type AC.
SELECT Stock1.NSN, Stock1.[Part No], Stock1.Noun, Stock1.[Type AC],
Stock1.[Qty On Hand]
FROM Stock1
WHERE (((Stock1.Noun) Is Null));

I have two tables. One is a linked table that is updated from an outside
program. The other is used inside access. The fields of Table 1 is as

[quoted text clipped - 4 lines]

to the query process so this is probably pretty simple but I don't have the
experience to get it to work.