Update or Append Data to a New Field in the Same Table

Hi Experts -

I'm trying to update a field in a table with the correct view; that is to show all Part Numbers in one field. If you look at my sample2 database; I have a field called PartNumber and one called PartNumber2 I would like both of these numbers in the same field named NewPartNumber in the same table.

Should I use an Update or Append query for this?

Thanks in Advance,
GeneBat
sample2.mdb
GeneBatAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
so basically we will disregard the values "RBB_Vendor" from PartNumber



UPDATE sample SET sample.NewPartNumber = IIF([PartNumber] Not Like "RBB_Vendor",[PartNumber] & [PartNumber2],[PartNumber2]);


are there any other values aside from "RBB_Vendor" that must be disregarded?
0
 
Rey Obrero (Capricorn1)Commented:
use an update query

update tablename
set [NewPartNumber]=[partnumber1] & [partnumber2]

how exactly do you want to show the newPartnumber?
0
 
GeneBatAuthor Commented:
Cap -
I forgot to write this; I don't want the RBB_Vendor text in the new field just numbers only.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Rey Obrero (Capricorn1)Commented:
post sample result..
0
 
GeneBatAuthor Commented:
here's a sample result. if you run the update query it put's everything in the field and not just the numbers only.
sample2.mdb
0
 
Rey Obrero (Capricorn1)Commented:
try this query

UPDATE sample SET sample.NewPartNumber =IIF(isnumeric([PartNumber]),[PartNumber] & [PartNumber2],[PartNumber2])
0
 
GeneBatAuthor Commented:
Darn, the real data could have a alpha and numeric in the PartNumber and PartNumber2 field see sample2.

Sorry about this Cap1?
sample2.mdb
0
 
Helen FeddemaCommented:
Generally, it is better to do this kind of thing in a query, because if you write the concatenated value to a field in a table, and one of the components changes, the concatenated value will be incorrect.
0
 
Rey Obrero (Capricorn1)Commented:
<Darn, the real data could have a alpha and numeric in the PartNumber and PartNumber2 field see sample2. >

so, what do you want to happen,  don't make me keep guessing

state the rules that need to be followed... in plain english

post sample values  and the result you want
0
 
GeneBatAuthor Commented:
Hi Helen_Feddema -

Thanks; I'm trying to get the query correct as in the same database I have attached.
0
 
GeneBatAuthor Commented:
What we're doing is correct it's just the the database field PartNumber could have a number that looks like this 13J13 so; isnumeric would not work.

PartNumber          PartNumber2     NewPartNumber
13J13                                                13J13
RRB_Vendor           14R14                 14R14
15T15                                                15T15

Like the example above.
Thanks in advance.
0
 
GeneBatAuthor Commented:
No there aren't...
Wonderful!
It works great!
Thanks so much Cap1.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.