Alter Data within a Table

[Raymond Clarke]

I have a field which contains data with a primary key and
provides the relationship with other tables.

Example of data

0300015 DKGMK4277

I am importing data from another database which only
contains DKGMK4277. Now, I need to delete 0300015 from the
field and keep the DKGMK4277. I have 60,000 records which
need this done. What is the best way of accomplishing
this.

 

[Roger]

I have a field which contains data with a primary key and
provides the relationship with other tables.

Example of data

0300015 DKGMK4277

I am importing data from another database which only
contains DKGMK4277. Now, I need to delete 0300015 from the
field and keep the DKGMK4277. I have 60,000 records which
need this done. What is the best way of accomplishing
this.

Preferably copy the entire database (mdb file) but as a minimum copy the
original table as a backup so that you can always revert to this table if
anything goes wrong!

Produce a select query which shows the key field and a calculated field.
The calculated field should use the "mid" function to just return the
required part of the key field.
eg. If the unwanted part is always 7 characters plus a space the wanted
part is everything to the right of the space so the calculation formula
would be Expr1: Mid([Field1],9). This will then return DKGMK4277 in the
second column of the query.
Run the query and ensure it works correctly. Then change the query type to
a Make-table query and run the query again to produce a temporary table.
Produce a new query with the original table and the new temporary table and
join the keyfields together. Drag the original keyfield down to the QBE
grid. Change the query type to an Update query and then in the "Update To"
box in the query grid, type in Expr1. Run the query and the keyfield should
then update to the new values from the temporary table.
Verify the new data is correct then delete the two queries and the temporary
table.

Note that this will only work if all records require the first 8 characters
to be deleted. If this varies then alternative methods should be used,
either using the mid/len functions or the find functions to filter the data
for the temporary table.
Alternatively, copy and paste the database field (column) into an Excel
spreadsheet - it should just fit at 60,000 records. Then use the Data/Text
to Columns function to split the data into two. You can specify the column
width to be used or use specific delimiters such as spaces. Then paste the
Excel data (two columns) back into a temp table in Access and update the
original table as described above.

HTH,

Roger

 

[John Vinson]

I have a field which contains data with a primary key and
provides the relationship with other tables.

Example of data

0300015 DKGMK4277

I am importing data from another database which only
contains DKGMK4277. Now, I need to delete 0300015 from the
field and keep the DKGMK4277. I have 60,000 records which
need this done. What is the best way of accomplishing
this.

An Update query will do this. You're not clear just what determines
what should and should not be deleted, though - one example doesn't
convey the possible variations!

If the imported data always contains a string of numerals, a single
blank, and then the value which should be retained, you can update the
field to

Mid([fieldname], InStr([fieldname], " ") + 1)

InStr will find the position of the first blank, and the substring
function Mid() will return the characters past that blank.

If the format of the field is different than I've assumed post back
with more examples.