String manipulation

[Eric G]

I have data in one field of a table that I'd like to update.
I hoping that someone here who is fluent in string manipulation can
lend a hand.

Most of the Rm no. data in the field consists of values such as:
101A, 102A, 103B, 105B, 105D, 106B, 104C, 104D etc.

There are also a few Rm nos. with the following:
GYM1, GYM2, 1GYM, 2GYM, X, and blank.

What I'd like to do is update the first set above so that the letter
is at the front of the data such as:
A101, A102, B103 etc.

If the second set above is altered as well, that's OK, but it isn't
required.

Thanks in advance for your help! Eric

 

[Ken Snell]

Try an SQL something like this:

UPDATE TableName
SET RoomNumberField = Right([RoomNumberField], 1)
& Left([RoomNumberField], Len([RoomNumberField]) - 1)
WHERE IsNumeric(Left([RoomNumberField], 3)) = True;

 

[Eric G]

Hi Ken,

Thanks very much for helping out!
Your SQL worked out perfectly.

Just one more question. I have four fields that need updating.
When I tried copying the code four times within the Update Query and
adjusting the field names (Per1, Per2, Per3, Per4) not all fields were
updated properly when I ran the query.

So for the time being I created four separate queries and ran them.
Is it possible to run just one to receive the same results?

Thanks again! Eric

Try an SQL something like this:

UPDATE TableName
SET RoomNumberField = Right([RoomNumberField], 1)
& Left([RoomNumberField], Len([RoomNumberField]) - 1)
WHERE IsNumeric(Left([RoomNumberField], 3)) = True;


--
Ken Snell
<MS ACCESS MVP>

I have data in one field of a table that I'd like to update.
I hoping that someone here who is fluent in string manipulation can
lend a hand.

Most of the Rm no. data in the field consists of values such as:
101A, 102A, 103B, 105B, 105D, 106B, 104C, 104D etc.

There are also a few Rm nos. with the following:
GYM1, GYM2, 1GYM, 2GYM, X, and blank.

What I'd like to do is update the first set above so that the letter
is at the front of the data such as:
A101, A102, B103 etc.

If the second set above is altered as well, that's OK, but it isn't
required.

Thanks in advance for your help! Eric

 

[Ken Snell]

So long as you'd need the WHERE clause to differentiate when a field should
be updated and when it should not be updated, then no, the table structure
that you have cited will not lend itself to a single query. The difficulty
is that the WHERE clause will apply to all records, and the four fields in a
single record may not all meet the same WHERE constraint. So running four
queries was the right solution.

Now, allow me to suggest that you review your table structure. A common
"flag" to indicate that your table is not normalized is when you are using
field names that embed data in them: Per1, Per2, etc. imply that you have a
separate field for each "period" within a single record. As you've already
noted, this separation has complicated a fairly simple query operation --
having to run a query four times instead of just once.

Instead of having four fields to identify the periods, you should have a
single record in the table for each period. You'd add a field to the table
for PeriodNumber, and then the Per1 field would be changed to RoomNumber (or
something like that), and you'd delete the Per2, Per3, and Per4 fields (be
sure to put all their values into new records into the table). Thus, each
record would be for just one period. Then the update query that we'd
originally discussed would have worked for all the "fields" -- records --
without having to run it four times.

You'll find many more situations where your current table structure is going
to complicate your life when you try to find information that may be
scattered among the different periods. Those queries won't just mean running
a single query four times, but rather will require more complex union
queries or more complex select queries.

--
Ken Snell
<MS ACCESS MVP>

Hi Ken,

Thanks very much for helping out!
Your SQL worked out perfectly.

Just one more question. I have four fields that need updating.
When I tried copying the code four times within the Update Query and
adjusting the field names (Per1, Per2, Per3, Per4) not all fields were
updated properly when I ran the query.

So for the time being I created four separate queries and ran them.
Is it possible to run just one to receive the same results?

Thanks again! Eric

Try an SQL something like this:

UPDATE TableName
SET RoomNumberField = Right([RoomNumberField], 1)
& Left([RoomNumberField], Len([RoomNumberField]) - 1)
WHERE IsNumeric(Left([RoomNumberField], 3)) = True;


--
Ken Snell
<MS ACCESS MVP>

I have data in one field of a table that I'd like to update.
I hoping that someone here who is fluent in string manipulation can
lend a hand.

Most of the Rm no. data in the field consists of values such as:
101A, 102A, 103B, 105B, 105D, 106B, 104C, 104D etc.

There are also a few Rm nos. with the following:
GYM1, GYM2, 1GYM, 2GYM, X, and blank.

What I'd like to do is update the first set above so that the letter
is at the front of the data such as:
A101, A102, B103 etc.

If the second set above is altered as well, that's OK, but it isn't
required.

Thanks in advance for your help! Eric

 

[Eric G]

Hi Ken,

Thanks for your reply.

I threw together the rooms into my Students table (Student#, LastName,
FirstName) on the fly without much thought about a year ago.
In another post, John Vinson discovered my 'un-normalized' table
structure and suggested I make changes to normalize it.

I know what basically needs to be done. The only problem is that I
can't easily obtain the data reports that would be required from
another database to get my input data for this db. That db admin
person is still learning the ropes and I don't have access rights to
that db.

This is really the only 'un-normalized' part of this db. And
everything is working quite well right now.
I may normalize the table this summer if I have some spare time. Then,
if I can get the data I need in the fall, I can go with the normailzed
version, if not, I can stick with the 'un-normalized' version.

Eric

So long as you'd need the WHERE clause to differentiate when a field should
be updated and when it should not be updated, then no, the table structure
that you have cited will not lend itself to a single query. The difficulty
is that the WHERE clause will apply to all records, and the four fields in a
single record may not all meet the same WHERE constraint. So running four
queries was the right solution.

Now, allow me to suggest that you review your table structure. A common
"flag" to indicate that your table is not normalized is when you are using
field names that embed data in them: Per1, Per2, etc. imply that you have a
separate field for each "period" within a single record. As you've already
noted, this separation has complicated a fairly simple query operation --
having to run a query four times instead of just once.

Instead of having four fields to identify the periods, you should have a
single record in the table for each period. You'd add a field to the table
for PeriodNumber, and then the Per1 field would be changed to RoomNumber (or
something like that), and you'd delete the Per2, Per3, and Per4 fields (be
sure to put all their values into new records into the table). Thus, each
record would be for just one period. Then the update query that we'd
originally discussed would have worked for all the "fields" -- records --
without having to run it four times.

You'll find many more situations where your current table structure is going
to complicate your life when you try to find information that may be
scattered among the different periods. Those queries won't just mean running
a single query four times, but rather will require more complex union
queries or more complex select queries.

--
Ken Snell
<MS ACCESS MVP>

Hi Ken,

Thanks very much for helping out!
Your SQL worked out perfectly.

Just one more question. I have four fields that need updating.
When I tried copying the code four times within the Update Query and
adjusting the field names (Per1, Per2, Per3, Per4) not all fields were
updated properly when I ran the query.

So for the time being I created four separate queries and ran them.
Is it possible to run just one to receive the same results?

Thanks again! Eric

Try an SQL something like this:

UPDATE TableName
SET RoomNumberField = Right([RoomNumberField], 1)
& Left([RoomNumberField], Len([RoomNumberField]) - 1)
WHERE IsNumeric(Left([RoomNumberField], 3)) = True;


--
Ken Snell
<MS ACCESS MVP>

I have data in one field of a table that I'd like to update.
I hoping that someone here who is fluent in string manipulation can
lend a hand.

Most of the Rm no. data in the field consists of values such as:
101A, 102A, 103B, 105B, 105D, 106B, 104C, 104D etc.

There are also a few Rm nos. with the following:
GYM1, GYM2, 1GYM, 2GYM, X, and blank.

What I'd like to do is update the first set above so that the letter
is at the front of the data such as:
A101, A102, B103 etc.

If the second set above is altered as well, that's OK, but it isn't
required.

Thanks in advance for your help! Eric

 

[Eric G]

Hi Ken,

Just to follow-up some more on normalizing if I go that route.
John was recommending a table structure something like:

A normalized structure would have three tables:

Students
StudentID
LastName
FirstName
<biographical information, nothing about classes>

Classes
ClassID
ClassName
ClassRoom
ClassPer
<other info about the class as a class>

Enrollment
StudentID
ClassID
Semester
==========================

Would a simpler approach of something like this work too?

RoomAssign
StudentID
PerNumber
RmNumber

Thanks! Eric

 

[Ken Snell]

The "simpler" approach can work so long as you note that you lose the
ability to capture the info that would be in John's recommendation (class
name, etc.).

Good luck.

 

[Eric G]

Thanks again Ken.

The "simpler" approach can work so long as you note that you lose the
ability to capture the info that would be in John's recommendation (class
name, etc.).

Good luck.