Access Query - two linked tables, how to copy value from one table to the other?

%3CLINGO-SUB%20id%3D%22lingo-sub-2200876%22%20slang%3D%22en-US%22%3EAccess%20Query%20-%20two%20linked%20tables%2C%20how%20to%20copy%20value%20from%20one%20table%20to%20the%20other%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2200876%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20two%20tables%20in%20Microsoft%20Access%20with%20the%20following%20fields%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSTRONG%3ETable1%20fields%20are%3A%3C%2FSTRONG%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EMachine_ID%3C%2FP%3E%3CP%3EPart_ID%3C%2FP%3E%3CP%3EQuantityInMachine%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSTRONG%3ETable2%20fields%20are%3A%3C%2FSTRONG%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EPart_ID%3C%2FP%3E%3CP%3EQuantity%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20every%20Part_ID%20value%20in%20Table1%2C%20there%20is%20a%20matching%20record%20in%20Table2%20that%20has%20the%20same%20value%20for%20Part_ID.%26nbsp%3B%20I%20am%20able%20to%20create%20a%20SELECT%20query%20that%20does%20an%20inner%20join%20between%20the%20tables%20on%20Part_ID.%26nbsp%3B%20When%20I%20do%20this%2C%20for%20every%20Part_ID%2C%20I%20see%20the%20Table1.QuantityInMachine%20field%20and%20the%20the%20Table2.Quantity%20field%20for%20every%20Part_ID.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20like%20to%20somehow%20modify%20this%20query%20so%20that%20for%20every%20record%20(every%20Part_ID)%20returned%20by%20the%20query%2C%20it%20will%20copy%20the%20value%20in%20Table2.Quantity%20to%20Table1.QuantityInMachine.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20not%20been%20able%20to%20figure%20out%20how%20to%20do%20this.%26nbsp%3B%20If%20anyone%20here%20can%20give%20me%20a%20clue%2C%20I%20would%20greatly%20appreciate%20it.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EThanks%20and%20best%20regards%2C%3CBR%20%2F%3EPaul%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2200876%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

Hi,

 

I have two tables in Microsoft Access with the following fields:

 

Table1 fields are:

Machine_ID

Part_ID

QuantityInMachine

 

Table2 fields are:

Part_ID

Quantity

 

For every Part_ID value in Table1, there is a matching record in Table2 that has the same value for Part_ID.  I am able to create a SELECT query that does an inner join between the tables on Part_ID.  When I do this, for every Part_ID, I see the Table1.QuantityInMachine field and the the Table2.Quantity field for every Part_ID. 

 

I'd like to somehow modify this query so that for every record (every Part_ID) returned by the query, it will copy the value in Table2.Quantity to Table1.QuantityInMachine.

 

I have not been able to figure out how to do this.  If anyone here can give me a clue, I would greatly appreciate it.

Thanks and best regards,
Paul

1 Reply
Replied to on Answers, with a request for complete table structures to work with.