I created an update query using two tables but it isn't updating--no error msg. I need to update the date field in one table (localupdate) with the date field in another table (setup). I set the field row to local date, the table to the localupdate table and the "update to" row to [setup].[updatedate]. Other than this, is there an easier way to accomplish this from within a macro?
update table field from another table (1 Viewer)
- Thread starter ellenr
- Start date
- Local time
- Today, 00:36
- Joined
- Oct 29, 2018
- Messages
- 21,473
Not an error message, but Access should tell you how many records were updated. It could say 0 records were updated. Did it say that?
- Local time
- Today, 02:36
- Joined
- Feb 28, 2001
- Messages
- 27,186
Is this a singular row to be updated? How do you know which record in the table to update? This is not an insignificant question, because if you don't specify the desired row correctly, you would easily have a result of no records updated.
If you could define a specific way to assure the required selectivity for a formal single-record UPDATE query, you could use a macro step to run the query. However, I'll also point out that unless there is a singular record as the target, then a macro would have just as much trouble targeting the right record as a query would - because ambiguity in the target will often kill the effectiveness of that operation.
If you could define a specific way to assure the required selectivity for a formal single-record UPDATE query, you could use a macro step to run the query. However, I'll also point out that unless there is a singular record as the target, then a macro would have just as much trouble targeting the right record as a query would - because ambiguity in the target will often kill the effectiveness of that operation.
I would say that telling stories about a query also includes showing its SQL statement, because this is the query definition.
If nothing is updated, this could be because, for example, there are no records in the target table due to filtering.
If nothing is updated, this could be because, for example, there are no records in the target table due to filtering.
Hi! Im experiencing a similar issue as above, I'm trying to update a phone number from one table to another, no error message just lets me know 7497 records will update but then column is blank. Am I leaving something out?
Most likely a WHERE clause.
Rule #1 of UPDATE queries---you probably don't need them. 95% of the time it is because of a poor table structure. Data in a relational database doesn't need to be moved or synched. It should exist in one table and then when needed in conjunction with data in another table a SELECT query should be made so you have a datasource with all needed data.
Rule #2 of UPDATE queries--make a backup. You are irreversible changing your data so give yourself a spot to reverse to in case things go sideways. Either backup the whole database or just the table you are updating.
Rule #3 of UPDATE queries--start with a SELECT query. You are irreversible changing your data so instead of just giving it a go and thinking you will get it right the first time start with a SELECT query to identify the records you will be working on. If the SELECT returns the right rows then you can convert the SELECT to an UPDATE and proceed. IF not, fix the SELECT until it has the correct records.
Again, if 7497 sounds high, you missed adding criteria.
Rule #1 of UPDATE queries---you probably don't need them. 95% of the time it is because of a poor table structure. Data in a relational database doesn't need to be moved or synched. It should exist in one table and then when needed in conjunction with data in another table a SELECT query should be made so you have a datasource with all needed data.
Rule #2 of UPDATE queries--make a backup. You are irreversible changing your data so give yourself a spot to reverse to in case things go sideways. Either backup the whole database or just the table you are updating.
Rule #3 of UPDATE queries--start with a SELECT query. You are irreversible changing your data so instead of just giving it a go and thinking you will get it right the first time start with a SELECT query to identify the records you will be working on. If the SELECT returns the right rows then you can convert the SELECT to an UPDATE and proceed. IF not, fix the SELECT until it has the correct records.
Again, if 7497 sounds high, you missed adding criteria.
