Printable View
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.
Yeah, sorry. VB and Access. Are you saying this can be accomplished entirely in access, with no coding?
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.
See this for help.
Or you can just choose SQL View from the View drop down and use the above query.Quote:
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.
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.Quote:
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~!
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.
The following code will run the stored update query held in the database from within vb. (i've never used the data environment)
Dim db As Database
Set db = OpenDatabase("c:\FullDatabasePath.mdb")
db.Execute ("nameOfStoredUpdateQueryInTheDatabase")
db.close