Replace data in field

  • Thread starter Thread starter anil
  • Start date Start date
A

anil

hi all
I have data in field Site as
ABC-600-X-01,ABC-600-X-02,ABC-600-Y-01,PQR-601-X-01.
where ABC,PQR are the short name of towns and 600,601 are their codes
respectively.
There are around 200 records in field.

I used to import data in this format from various locations , e.g Town
ABC will send me in format ABC-600-X-01, ...so on and PQR will send in
PQR-601-X-01,....so on.

all data comes together at Center on paper and they enter in their
database and from there i recieve as .csv file.

Now from Center they have removed ABC,PQR and send it in format
600-X-01,600-X-02,601-X-01 and so on.

Is there any way so that I can fix it.
I have ABC and 600 both as seperate fields.

I hope I have made clear

thanks
anil
 
When you get a file from the "Center", does it contain data from both ABC
and PQR? Or is there a file for each town?

In other words, when you get a file, how do you know that a record (or the
file) relates to a specific town?
 
hi john

yes I recieve as .csv file and the data consists of 34 towns e.g
ABC,PQR,XYZ ...... so on.Actually all towns sends data to center and
from there I recieves after adding some more information as different
fields like result value,units etc.

the record relates to specific town as the sitecode is ABC-600-X-01
where ABC is 3 letters of town,600 is code for that town,X is type and
01 is number for that site.

actually it has been a mistake that instead of ABC-600-X-01 it comes
600-X-01 ,they missed first digits of code.This has to happen for at
least 4 times or more as the data is already feeded .

I have done this time in Excel as Replace 600 with ABC-600 but it is
time consuming and frustating.also chances of getting wrong.

can we do some thing in Access or I have to do same thing in excel.

anil
 
If I understand you correctly, the data you received was incorrectly formed
and you need to fix it. If this is just an intermittent thing, you might be
better off just fixing the data in Excel as you do now.

Do the first three characters ("ABC)" always have a one to one match to the
three digits ("600")? If there is always a one to one match then you need a
table that has the towncode (three numbers) and the townLetters (three
letters). With that table joined to the incoming data, you could update
the data submitted with an update query.

Assumption:
--CSV File is imported into a work table
--There is a table (TownTable) with the TownCode (3-char field of number
characters) and TownLetters (3-character field of letter characters)

UPDATE CSVImportFile INNER JOIN TownTable
ON CSVImportFile .SiteCode Like TownTable.TownCode & "*"
SET CSVImportFile .SiteCode = [TownTable].[TownLetters] & "-" &
[CSVImportFile ].[SiteCode]
 
Thanks John
It worked (with slight change).Although I have to change for 3-5 times
but I learned new thing,which was great.

Actually i was trying to figure out some thing like ---
" [TownTable].[TownLetters] & "-" & [CSVImportFile ].[SiteCode] "
but was not able.this also solved one of my old problem which I was
doing differently.
Thanks again
anil
 
Back
Top