Changing Values in Fields

[CC]

I import text files that contain values with suffixes, and the suffix could
be any two alpha characters i.e.:
D1234-AA
D1234-AB
D1234-AC
D2345-AA
D2345-AB

I need to import the data and omit the the suffix of the first number, i.e.:
D1234
D1234-AB
D1234-AC
D2345
D2345-AB

Can anyone provide direction on how to accomplish this?

 

[John Spencer MVP]

I don't know that you can do it during the import, but you could use and
update query to fix it after the import

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

UPDATE [YourTable]
SET [YourField] = Replace([YourField],"-AA","")
WHERE [YourField] Like "*-AA"

In the query design view
-- add your table
-- add your field to the list of field to display
-- set the criteria to
LIKE "*-AA"
-- Select Update Query from the query menu
-- Enter the following in the UPDATE To box
Replace([YourTable].[YourField],"-AA","")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[CC]

The first value does not always end with '-AA'. It could start end with
'-BD'. It varies. I need to identify the first value and drop the suffix,
regardless of what that suffix may be.

I don't know that you can do it during the import, but you could use and
update query to fix it after the import

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

UPDATE [YourTable]
SET [YourField] = Replace([YourField],"-AA","")
WHERE [YourField] Like "*-AA"

In the query design view
-- add your table
-- add your field to the list of field to display
-- set the criteria to
LIKE "*-AA"
-- Select Update Query from the query menu
-- Enter the following in the UPDATE To box
Replace([YourTable].[YourField],"-AA","")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I import text files that contain values with suffixes, and the suffix could
be any two alpha characters i.e.:
D1234-AA
D1234-AB
D1234-AC
D2345-AA
D2345-AB

I need to import the data and omit the the suffix of the first number, i.e.:
D1234
D1234-AB
D1234-AC
D2345
D2345-AB

Can anyone provide direction on how to accomplish this?

 

[John Spencer]

Ok, then the question becomes how do you identify the first value. Is
it the value with the lowest alphabetic value (first one in sort order
of the group)? Also do the values always have the same length and
structure (Letter, 4 numbers, a dash, and two more letters)?

If the pattern is followed and the record to change is the first one
alphabetically in the group, you could use a query like this.

UPDATE YourTable
SET SomeField = Left([YourTable].[SomeField],5)
WHERE SomeField in (
SELECT Min(SomeField)
FROM YourTable
GROUP BY Left(SomeField,5))


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[CC]

It is the value with the lowest alpha suffix. The values could start with one
or two alphas and the suffix may or may not contain a dash, i.e. values could
be:

ANNNN-AA
ANNNNAA
AANNNAA
AANNN-AA

Where A = alpha character and N = numeric character. I have queries that
strip the suffix of all of these and store the new value in a separate field.
You SQL worked great with the first test - Thanks! I need to know if the "-"
in my values will affect hwo your SQL sorts/groups the records, i.e. will
'D1234-AA' be sorted just like 'D1234AA'?

Ok, then the question becomes how do you identify the first value. Is
it the value with the lowest alphabetic value (first one in sort order
of the group)? Also do the values always have the same length and
structure (Letter, 4 numbers, a dash, and two more letters)?

If the pattern is followed and the record to change is the first one
alphabetically in the group, you could use a query like this.

UPDATE YourTable
SET SomeField = Left([YourTable].[SomeField],5)
WHERE SomeField in (
SELECT Min(SomeField)
FROM YourTable
GROUP BY Left(SomeField,5))


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

The first value does not always end with '-AA'. It could start end with
'-BD'. It varies. I need to identify the first value and drop the suffix,
regardless of what that suffix may be.

:

 

[John Spencer MVP]

No, the sort will be different for D1234-AA and 'D1234AA'. However it still
appears that you want the first five characters of the field, so the suggested
code may work for you based on the data.

You might add a field to your table named newID and run the update query to
populate it. THEN check the results to see if you get the desired value.

You could try to strip out the dash before doing the comparison.

UPDATE YourTable
SET SomeField= Left([YourTable].[SomeField],5)
WHERE Replace(SomeField,"-","") in
SELECT Min(Replace(SomeField,"-",""))
FROM YourTable
GROUP BY Left(SomeField,5)

If you have records like
A1234-CC
A1234CC
And the dash has meaning then there could be a problem.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

It is the value with the lowest alpha suffix. The values could start with one
or two alphas and the suffix may or may not contain a dash, i.e. values could
be:

ANNNN-AA
ANNNNAA
AANNNAA
AANNN-AA

Where A = alpha character and N = numeric character. I have queries that
strip the suffix of all of these and store the new value in a separate field.
You SQL worked great with the first test - Thanks! I need to know if the "-"
in my values will affect hwo your SQL sorts/groups the records, i.e. will
'D1234-AA' be sorted just like 'D1234AA'?

Ok, then the question becomes how do you identify the first value. Is
it the value with the lowest alphabetic value (first one in sort order
of the group)? Also do the values always have the same length and
structure (Letter, 4 numbers, a dash, and two more letters)?

If the pattern is followed and the record to change is the first one
alphabetically in the group, you could use a query like this.

UPDATE YourTable
SET SomeField = Left([YourTable].[SomeField],5)
WHERE SomeField in (
SELECT Min(SomeField)
FROM YourTable
GROUP BY Left(SomeField,5))


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

The first value does not always end with '-AA'. It could start end with
'-BD'. It varies. I need to identify the first value and drop the suffix,
regardless of what that suffix may be.

: