Update query to copy one field to anther field in the same table (1 Viewer)

slharman1

Member
Local time
Today, 08:39
Joined
Mar 8, 2021
Messages
467
I have a DB that takes a quote number and turns it into an order using the same Quote number as the order number. I have added a quote number field to the order table and need to copy all of the current order numbers to the new field in the same table.
I can't seem to get the update query to update the records.
In the Update to field in for the quotenumber field , I have =[ordernumber] but it doesn't work. I have tried putting null in the criteria field since that field is empty. still doesn't work
I need to do this so I can keep all of my code written and just add the quote number field, from here forward there will be a different number the orders and quotes.
Trying to fix a bad design from the start.
Thanks
 
Last edited:

plog

Banishment Pending
Local time
Today, 08:39
Joined
May 11, 2011
Messages
11,611
This sounds wrong, but I need specifics. Actions queries (UPDATE, INSERT, DELETE) are red flags that you have not built your tables correctly or understand how relational databases are to work.

Why can't you just add a Quote number to an existing Order record instead of moving data around?
 

slharman1

Member
Local time
Today, 08:39
Joined
Mar 8, 2021
Messages
467
This sounds wrong, but I need specifics. Actions queries (UPDATE, INSERT, DELETE) are red flags that you have not built your tables correctly or understand how relational databases are to work.

Why can't you just add a Quote number to an existing Order record instead of moving data around?
You are correct, I have edited my original post - it was bad DB design, I want to fix it but need to keep original orders in tack. I just need to do this update query one time.
I need to copy the order number to the quote number field for the current records, then I will delete the update query.
What is the best way to do this?
 

plog

Banishment Pending
Local time
Today, 08:39
Joined
May 11, 2011
Messages
11,611
Good to hear. Can you provide sample data? Either a copy of a database or just type in here what your data looks like and what you want it to become?
 

slharman1

Member
Local time
Today, 08:39
Joined
Mar 8, 2021
Messages
467
Good to hear. Can you provide sample data? Either a copy of a database or just type in here what your data looks like and what you want it to become?
In the orders table I have three fields, OrderID, OrderNumber, and QuoteNumber.
QuoteNumber was just added and is empty for all 200 records.
I want to copy the OrderNumber Field to the QuoteNumber Field
 

plog

Banishment Pending
Local time
Today, 08:39
Joined
May 11, 2011
Messages
11,611
If its just 200 records you can open the table, right click OrderNumber and paste into Quote Number.

You can also open a new query, add your table, make it an UPDATE query, bring down QuoteNumber into the query and under it have it update to [OrderNumber].

No matter which method, always make a backup prior to running any action query.
 

slharman1

Member
Local time
Today, 08:39
Joined
Mar 8, 2021
Messages
467
If its just 200 records you can open the table, right click OrderNumber and paste into Quote Number.

You can also open a new query, add your table, make it an UPDATE query, bring down QuoteNumber into the query and under it have it update to [OrderNumber].

No matter which method, always make a backup prior to running any action query.
Thanks, I’ll give it a try. Do you make a back up of the table or the db?
The reason I ask is because I run and action query to update pricing all the time and don’t make a backup.
 

plog

Banishment Pending
Local time
Today, 08:39
Joined
May 11, 2011
Messages
11,611
Either would do it. I do the whole database if its one other have access to.
 

slharman1

Member
Local time
Today, 08:39
Joined
Mar 8, 2021
Messages
467
If its just 200 records you can open the table, right click OrderNumber and paste into Quote Number.

You can also open a new query, add your table, make it an UPDATE query, bring down QuoteNumber into the query and under it have it update to [OrderNumber].

No matter which method, always make a backup prior to running any action query
Update query doesn't work, but copying and pasting did the trick, Thanks
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:39
Joined
Feb 19, 2002
Messages
42,970
Copy/Paste is a pretty scary way to do this in a relational database. For yourself, you need to figure out what you were doing wrong with the update query.

Open the QBE. Add the table you want to update.
Select the field you want to update.
Change the query type from select to update
In the UpdateTo cell, type in the name of the field you want to copy from. Be careful here. Access is going to "help" you and its "help" is going to make the query not work. Make sure that the Column name in the UpdateTo cell is NOT enclosed in quotes. That turns it into a string. O eliminate the quotes, you probably will have to substitute them with square brackets.

The MS Access team made a decision here which annoys me to no end. Instead of assuming that you would be entering a column name, they assumed you would be entering a literal value so they automatically enclose what you type in quotes UNLESS you specifically type the square brackets. Since we always teach you to create column names that conform to standards and do NOT require square brackets, you wouldn't thing you needed to type them here but you do - regardless of whether you are using best practice naming standards or not. The problem with the assumption is it will not throw an error. It willl just update the column to the name of the column instead of its value. Whereas if they had assumed you wanted a column name, you would get an error if the column name wasn't found. INMNSHO, it is better to get an error than to perform a potentially invalid update that works as long as the field you are updating is text so I would have made the opposite assumption.
 
Last edited:

Users who are viewing this thread

Top Bottom