Solved

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

Posted on 2013-01-17
12
423 Views
Last Modified: 2013-01-17
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
Comment
Question by:GeneBat
  • 6
  • 5
12 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38788524
use an update query

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

how exactly do you want to show the newPartnumber?
0
 

Author Comment

by:GeneBat
ID: 38788594
Cap -
I forgot to write this; I don't want the RBB_Vendor text in the new field just numbers only.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38788609
post sample result..
0
 

Author Comment

by:GeneBat
ID: 38788645
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38788658
try this query

UPDATE sample SET sample.NewPartNumber =IIF(isnumeric([PartNumber]),[PartNumber] & [PartNumber2],[PartNumber2])
0
 

Author Comment

by:GeneBat
ID: 38788756
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 38788761
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38788784
<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
 

Author Comment

by:GeneBat
ID: 38788798
Hi Helen_Feddema -

Thanks; I'm trying to get the query correct as in the same database I have attached.
0
 

Author Comment

by:GeneBat
ID: 38788841
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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 38788877
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
 

Author Closing Comment

by:GeneBat
ID: 38788929
No there aren't...
Wonderful!
It works great!
Thanks so much Cap1.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now