Calculate value by using data from another table

[Scythed]

Hi there, I am trying to make a query that will grab the values from a field in one table, find the remainder left when it is subtracted from 50, then place that value into another field in another table. Does anyone know how to do this please? It is for a Year 12 assignment so it does not have to be overly complex, any help would be greatly appreciated. Thank you.

 

[georgedwilkinson]

insert into theothertable (NewCalculatedField)
select (50 - valuefromafield) from onetable;

 

[Scythed]

Thank you very much, just one quick question, when you say valuefromfield does that mean the name of the field in which the original number is located?
Basically this is the structure of my database concerning this query.
Tables - (Stock Ordered) and (Stock Available)
Fields - (Length Ordered) and (Length Remaining)

This is how I rewrote that query that you gave me, is this correct?

insert into [Stock Ordered] ([Length Ordered])
select (50 - [Length Remaining]) from [Stock Available];

 

[Banana]

RE: your question about naming, yes.

RE: your query's SQL: Why not give it a try?

 

[Scythed]

This is the error I get when I try to run it.
http://www.uploadhouse.com/viewfile.php?id=2521165&PHPSESSID=b0bd97c60400c9dd40a81d307d9067b1
And if I say yes then none of the information in my database gets changed

 

[Banana]

This failed because not all required information were supplied; you have a relationship for which you must satisfy with a foreign key. Therefore, your SQL should pull the key from the parent table so Access know that this record is related to which record in parent table.

 

[Scythed]

This is the code that I end up with when I connect the two tables using a common field of RollID

INSERT INTO [Stock Ordered] ( [Length Ordered] )
SELECT (50-[Length Remaining])
FROM [Stock Available] INNER JOIN [Stock Ordered] ON [Stock Available].[Roll ID]=[Stock Ordered].[Roll ID];

But it still does not work, can you see where I am going wrong?