Replacing data within a column in table.

[opostal]

I have an older database that has 18000 records or so in it. I have recently begun using a new software that will automatically sort data for me and post it into a database. This is all perfect. What I want to do is merge the old database with the new. My problem arises in that the new database and old database have the same quantity of columns but the new database requires two letters to be added to the front of the data in each cell identifying the type. Is there a simple query or macro I could run that would allow me to go through column 1 and change the item there to the original value preceded by a two letter format?

For example:
old
Column 1
1234

New
Column 1
AB1234

Thank you in advance.

 

[Scooterbug]

Do an update query.

UPDATE Table1 SET Table1.Column1 = "AB" & [Column1];

Note that Column1 must be a text field in the table.

 

[opostal]

Boy do I hate to sound stupid but I am completely unsuccessful at solving this. Thank you for the information. In an effort to make this easy, I went to a single table and made a single query on one field. The field is just a sequential numbering and is not the index field. It is categorized as text. I used your input several different ways, rebuilt it using the editor etc etc. The only thing I was able to accomplish was to get the query to relist the numbers again without adding the two letters to the value. I simply wanted it to add two letters to the beginning of the value in that field. Here is an example of what I have in the update to block:

UPDATE [METR]
SET [METR].[Project]="IY" & [Project]

I have tried about a dozen variations and can either get the result to change the entire column to a zero or change nothing at all. It must be something incredibly small and simple that I am missing. Thanks again for any assistance.

 

[Scooterbug]

Hmmm....I just tried it using your table name, field name and the sql you posted and it worked just fine. Are you sure that in the table that the Project field is set as Text?

 

[opostal]

Hmm I am still playing with it. Will update tomorrow thank you for the feedback.

 

[neileg]

I don't think you're hearing what Scooterbug is saying. If [Project] is a number datatype you can't add letters to it. You will either need to change the datatype or use a new column set to text to create the new ID and then delete the number column.

 

[opostal]

No actually I got that. The fields are all Text type fields less the autoindex which is a number. Still working with it but thank you for the feedback will let you know. Just feeling a little more foolish each attempt . The constant result I wind up with is converting the entire column to a 0.

Thank you though as it is likely that I may miss the simplest issue...lol

 

[Scooterbug]

Can you post a copy of the database with just the table you are working with? Make sure it's an MDB and not an ACCB....you will get better responses that way.

 

[opostal]

Well I hate to admit defeat but here is a slim version of the dbase. I have only left the first 193-ish entries in the database. I also left another attempt at the update query. Did not want this to be too big. My results are always a full column of zeros. Thanks again. I can take a good laughing at if it is something small and easy that I missed...

 

[opostal]

I suppose it would help if I updated the intent. I would simply like the following fields to have a two letter addition on them:

Inventory
Job
PO
Material
Heat
Size
Thickness

 

[Scooterbug]

Do an update query.

UPDATE Table1 SET Table1.Column1 = "AB" & [Column1];

Note that Column1 must be a text field in the table.

the above code is the Sql of the actual query (Which can be viewed by clicking on the first button on the ribbon...it will say "Views" when you hover your mouse over it). What goes in the actual field in the QBE version (That is the Window with the field names in the box and the grid below) is this for the Update To line:

"IY" & [Job]