replace filed's text with correspnding number

S

steallan

Active Member
Joined
Oct 20, 2004
Messages
308
HI
Ive got a column in a table that has descriptions in it, with lots of duplicates. Another table has the descriptions (no duplicates) and a corresponding number value. want to replace the descriptions in the first table with the number values.
Is this something access does or am i better off exporting to excel and doing it there then importing the data back?
Hope thats clear enough.
thx
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
Access won't automatically do it - you need to do a bit of work yourself.

Create a new column in the table with the duplicates, say DescriptionID.

Now create a query where the two tables are joined by the description fields.
Run it to check it's giving the right results.

Now change this query to an Update query and add the
DescriptionID field form the main table.

In the Update To part of the criteria put the DescriptionID from
the descriptions table.

Run this query update the DescriptionID in the main table to the corresponding value from the descriptions table.

This might sound complicated but once you've done it a
few times you get the hang of it and you are on your way to
having a normalized database.

If you can give more details about your tables I can show you
what the queries should look like.
 
Upvote 0
<html xmlns:eek:="urn:schemas-microsoft-com:eek:ffice:eek:ffice"xmlns:x="urn:schemas-microsoft-com:eek:ffice:excel"xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><meta name=ProgId content=Excel.Sheet><meta name=Generator content="Microsoft Excel 9"><link rel=File-List href="./Table1_files/filelist.xml"><link rel=Edit-Time-Data href="./Table1_files/editdata.mso"><link rel=OLE-Object-Data href="./Table1_files/oledata.mso"><style></style></head><body link=blue vlink=purple><table x:str border=0 cellpadding=0 cellspacing=0 width=731 style='border-collapse: collapse;table-layout:fixed;width:550pt'> <col width=65 style='mso-width-source:userset;mso-width-alt:2773;width:49pt'> <col width=97 style='mso-width-source:userset;mso-width-alt:4138;width:73pt'> <col width=126 style='mso-width-source:userset;mso-width-alt:5376;width:95pt'> <col width=136 style='mso-width-source:userset;mso-width-alt:5802;width:102pt'> <col width=70 style='mso-width-source:userset;mso-width-alt:2986;width:53pt'> <col width=69 style='mso-width-source:userset;mso-width-alt:2944;width:52pt'> <col width=56 span=3 style='width:42pt'> <tr height=15 style='height:11.25pt'> <td height=15 colspan=2 width=162 style='height:11.25pt;mso-ignore:colspan; width:122pt'>Here is a small example:</td> <td width=126 style='width:95pt'></td> <td width=136 style='width:102pt'></td> <td width=70 style='width:53pt'></td> <td width=69 style='width:52pt'></td> <td width=56 style='width:42pt'></td> <td width=56 style='width:42pt'></td> <td width=56 style='width:42pt'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 colspan=9 style='height:11.25pt;mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 style='height:11.25pt'>Table1</td> <td colspan=3 style='mso-ignore:colspan'></td> <td class=xl32 width=70 style='width:53pt'>Descriptions</td> <td colspan=4 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl29 style='height:11.25pt'>Product</td> <td class=xl29 style='border-left:none'>Description</td> <td class=xl29 style='border-left:none'>DescriptionID</td> <td></td> <td class=xl29>DescriptionID</td> <td class=xl29 style='border-left:none'>Description</td> <td colspan=3 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl30 width=65 style='height:11.25pt;width:49pt'>Product1</td> <td class=xl30 width=97 style='border-left:none;width:73pt'>Description2</td> <td colspan=2 style='mso-ignore:colspan'></td> <td class=xl31 width=70 style='width:53pt' x:num>1</td> <td class=xl30 width=69 style='border-left:none;width:52pt'>Description1</td> <td colspan=3 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl30 width=65 style='height:11.25pt;border-top:none; width:49pt'>Product2</td> <td class=xl30 width=97 style='border-top:none;border-left:none;width:73pt'>Description1</td> <td colspan=2 style='mso-ignore:colspan'></td> <td class=xl31 width=70 style='border-top:none;width:53pt' x:num>2</td> <td class=xl30 width=69 style='border-top:none;border-left:none;width:52pt'>Description2</td> <td colspan=3 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl30 width=65 style='height:11.25pt;border-top:none; width:49pt'>Product3</td> <td class=xl30 width=97 style='border-top:none;border-left:none;width:73pt'>Description2</td> <td colspan=7 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl30 width=65 style='height:11.25pt;border-top:none; width:49pt'>Product4</td> <td class=xl30 width=97 style='border-top:none;border-left:none;width:73pt'>Description1</td> <td colspan=7 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 colspan=2 class=xl32 style='height:11.25pt;mso-ignore:colspan'> </td> <td colspan=7 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 style='height:11.25pt'>Query1</td> <td colspan=8 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 colspan=9 style='height:11.25pt;mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 colspan=5 style='height:11.25pt;mso-ignore:colspan'>SELECT Table1.Product, Table1.Description, Descriptions.Description, Descriptions.DescriptionID</td> <td colspan=4 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 colspan=5 style='height:11.25pt;mso-ignore:colspan'>FROM Table1 LEFT JOIN Descriptions ON Table1.Description = Descriptions.Description;</td> <td colspan=4 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 colspan=9 style='height:11.25pt;mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl29 style='height:11.25pt'>Product</td> <td class=xl29 style='border-left:none'>Table1.Description</td> <td class=xl29 style='border-left:none'>Descriptions.Description</td> <td class=xl29 style='border-left:none'>DescriptionID</td> <td colspan=5 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl30 width=65 style='height:11.25pt;width:49pt'>Product1</td> <td class=xl30 width=97 style='border-left:none;width:73pt'>Description2</td> <td class=xl30 width=126 style='border-left:none;width:95pt'>Description2</td> <td class=xl31 width=136 style='border-left:none;width:102pt' x:num>2</td> <td colspan=5 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl30 width=65 style='height:11.25pt;border-top:none; width:49pt'>Product2</td> <td class=xl30 width=97 style='border-top:none;border-left:none;width:73pt'>Description1</td> <td class=xl30 width=126 style='border-top:none;border-left:none;width:95pt'>Description1</td> <td class=xl31 width=136 style='border-top:none;border-left:none;width:102pt' x:num>1</td> <td colspan=5 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl30 width=65 style='height:11.25pt;border-top:none; width:49pt'>Product3</td> <td class=xl30 width=97 style='border-top:none;border-left:none;width:73pt'>Description2</td> <td class=xl30 width=126 style='border-top:none;border-left:none;width:95pt'>Description2</td> <td class=xl31 width=136 style='border-top:none;border-left:none;width:102pt' x:num>2</td> <td colspan=5 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl30 width=65 style='height:11.25pt;border-top:none; width:49pt'>Product4</td> <td class=xl30 width=97 style='border-top:none;border-left:none;width:73pt'>Description1</td> <td class=xl30 width=126 style='border-top:none;border-left:none;width:95pt'>Description1</td> <td class=xl31 width=136 style='border-top:none;border-left:none;width:102pt' x:num>1</td> <td colspan=5 style='mso-ignore:colspan'></td> </tr> <tr height=30 style='height:22.5pt;mso-xlrowspan:2'> <td height=30 colspan=9 style='height:22.5pt;mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 colspan=2 style='height:11.25pt;mso-ignore:colspan'>Update Query</td> <td colspan=7 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 colspan=9 style='height:11.25pt;mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 colspan=9 style='height:11.25pt;mso-ignore:colspan'>UPDATE Table1 LEFT JOIN Descriptions ON Table1.Description = Descriptions.Description SET Table1.DescriptionID = [Descriptions]![DescriptionID];</td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 colspan=9 style='height:11.25pt;mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 colspan=3 style='height:11.25pt;mso-ignore:colspan'>Table1(after running the update query)</td> <td colspan=6 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl29 style='height:11.25pt'>Product</td> <td class=xl29 style='border-left:none'>Description</td> <td class=xl29 style='border-left:none'>DescriptionID</td> <td colspan=6 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl30 width=65 style='height:11.25pt;width:49pt'>Product2</td> <td class=xl30 width=97 style='border-left:none;width:73pt'>Description1</td> <td class=xl31 width=126 style='border-left:none;width:95pt' x:num>1</td> <td colspan=6 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl30 width=65 style='height:11.25pt;border-top:none; width:49pt'>Product4</td> <td class=xl30 width=97 style='border-top:none;border-left:none;width:73pt'>Description1</td> <td class=xl31 width=126 style='border-top:none;border-left:none;width:95pt' x:num>1</td> <td colspan=6 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl30 width=65 style='height:11.25pt;border-top:none; width:49pt'>Product1</td> <td class=xl30 width=97 style='border-top:none;border-left:none;width:73pt'>Description2</td> <td class=xl31 width=126 style='border-top:none;border-left:none;width:95pt' x:num>2</td> <td colspan=6 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl30 width=65 style='height:11.25pt;border-top:none; width:49pt'>Product3</td> <td class=xl30 width=97 style='border-top:none;border-left:none;width:73pt'>Description2</td> <td class=xl31 width=126 style='border-top:none;border-left:none;width:95pt' x:num>2</td> <td colspan=6 style='mso-ignore:colspan'></td> </tr> <![if supportMisalignedColumns]> <tr height=0 style='display:none'> <td width=65 style='width:49pt'></td> <td width=97 style='width:73pt'></td> <td width=126 style='width:95pt'></td> <td width=136 style='width:102pt'></td> <td width=70 style='width:53pt'></td> <td width=69 style='width:52pt'></td> <td width=56 style='width:42pt'></td> <td width=56 style='width:42pt'></td> <td width=56 style='width:42pt'></td> </tr> <![endif]></table></body></html>
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
issue with Autogenerated custom serial number
Replies
3
Views
633
Micron
M
R
  • Solved
Late Binding: extract Access to Excel with formatting
2 3
Replies
22
Views
4K
roelandwatteeuw
R
M
  • Solved
Need review of code to select random record from table
2
Replies
10
Views
1K
Micron
M
A
  • Solved
Change table field text based on field in other table
Replies
2
Views
700
arcaidius
A
R
  • Solved
DateAdd variable with Text Field
2
Replies
11
Views
2K
Micron
M

Forum statistics

Threads
1,221,831
Messages
6,162,242
Members
451,756
Latest member
tommyw

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top