hot to change the values of a string in an update query

[Guest]

I have a table called StudentRoom which lists all the rooms that are
available to students to let, when the table was designed I used the
following to differentiate each room and become the primary key:

City/building/block/flat/room

so 'Lei/fi/A/001/1' would translate to a room in Leicester, Filbert
building, block A, flat 001, room 1.

With hindsight we now know we dont need the city part of this primary key
and i need to update all the records so that it is trimmed from the above to
'fi/A/001/1', how can i do that? its stored as a sting in the form fiA0011 at
the moment and the slashes get put in when its read to an output.

Have no idea how to change a string like this in a query!! can do it using
VB but how could i go through all the records?

With much thanks

Amit

 

[Guest]

Hi Amit,

First, I am not criticizing your data model. I would have field City,
Building, Block, Flat, and Room in my table and use them as Primary key (you
can have more than 1 field to be your primary key).

To fix your problem. Is City/building/block/flat/room is all coded the same
way? That mean, you always have 3 characters for City, 2 characters for
Building, 1 characters for Block, 3 characters for Flat, and 1 characters for
Room. If that is the case, parsing the string is easy. Just use function
“Mid†(use help to find out more help or syntax). If that is not the case,
it will much more difficult.

 

[Marshall Barton]

I have a table called StudentRoom which lists all the rooms that are
available to students to let, when the table was designed I used the
following to differentiate each room and become the primary key:

City/building/block/flat/room

so 'Lei/fi/A/001/1' would translate to a room in Leicester, Filbert
building, block A, flat 001, room 1.

With hindsight we now know we dont need the city part of this primary key
and i need to update all the records so that it is trimmed from the above to
'fi/A/001/1', how can i do that? its stored as a sting in the form fiA0011 at
the moment and the slashes get put in when its read to an output.

Have no idea how to change a string like this in a query!! can do it using
VB but how could i go through all the records?

Use an UPDATE query:

UPDATE StudentRoom
SET roomID = Mid(roomID, 4)
WHERE Len(roomID) =10

Be sure to test this on a **copy** of your table.

 

[Guest]

Thanks Marshall, I got it to run on my test DB and have updated the
StudentsRoom table. I had thought of using the Mid string command but ive
only ever used it in VB and didnt realise that i could use it in a Query!

Many thanks,

Amit