I want to reduce a field quantity, by the quantity in another field, on another table. I am using the same data environment with bound controls for each. I figure I need to add that table to the query, but I'm not sure of the algorithm, nor where to put it. Can anyone point me in the right direction?
You need to explain your setup more. Are you in VB? Access?
in Access:
Table1 has a number field called 'number' I want to replace this value with the 'age' value from table2. Based on the Id fields been equal. (you could use name).
UPDATE Table1 INNER JOIN table2 ON Table1.ID = table2.ID SET Table1.[number] = [number]-[table2].[age];
Adam.
Last edited by adamgray1209; Nov 6th, 2002 at 11:18 AM.
Yes. Queries. Open new in design view. Add the 2 tables. Make the link. Menu>Query change to update query. doubleclick only the field you want to change, in the UpdateTo field, put [fieldYouHaveJustSelected] - [secondTableName].[fieldToTakeMinus] . Then select Menu>Query>Run. Be sure to copy your tables first in case of error!
Originally posted by adamgray1209 Yes. Queries. Open new in design view. Add the 2 tables. Make the link. Menu>Query change to update query. doubleclick only the field you want to change, in the UpdateTo field, put [fieldYouHaveJustSelected] - [secondTableName].[fieldToTakeMinus] . Then select Menu>Query>Run. Be sure to copy your tables first in case of error!
Adam.
Or you can just choose SQL View from the View drop down and use the above query.
Originally posted by DUNSEL Thank you one and all. I was misled into believing it had to be coded in VB, by an instructor, no less~!
The instructor is not wrong necessarily. This could be done from vb if it is part of a bigger routine. But if it is a one time thing then you would just use access.
Well, not a one time thing, in the sense that I want all changes in the field subtracted, to be reflected in the one subtracted from. I'm new to programming ( student at Chubb, the IT school that sounds like a fat farm) and I was sure there was a way to do it in Access alone, with the simple equation you gentlemen posted, but I was told it was not so.
One last question on this; If I'm using a data environment, can I create the query in the SQL builder of the command, and if so, is the SQL syntax different? I tried and it rejected it.
Reply With Quote
