[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 439
  • Last Modified:

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
0
GeneBat
Asked:
GeneBat
  • 6
  • 5
1 Solution
 
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
 
Rey Obrero (Capricorn1)Commented:
post sample result..
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
Rey Obrero (Capricorn1)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
 
GeneBatAuthor Commented:
No there aren't...
Wonderful!
It works great!
Thanks so much Cap1.
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now