Update query

[Guest]

I have a table that contains prison bunk numbers:

1-101B
1-101T
1-102B and so on

1 means housing unit one
hyphen
101 is the cell number
B or T is bottom or top bunk

I have unit one done and it contains cells 101B through 262T inclusive. How
would I write an expression for an update query to create housing units
2,3,4,5,6 and 7 in the same table? Cell numbers would all be the same.
Many thanx!

Rip

 

[Ken Snell [MVP]]

Before perpetuating a design problem, may I suggest that you redo the table
structure? Instead of having a single field with a value of 1-101B, split it
into three fields:
UnitNumber
CellNumber
BunkID

UnitNumber would contain 1, CellNumber would contain 101, and BunkID would
contain B.

You can concatenate these values into a single string for display or
printing.

It's much easier, and more appropriate, to have "atomic" (discrete) data in
separate fields and then to "update" or modify or increment from there.

 

[Guest]

This has been considered, but will not do for our purposes. Time and space
prevents me from explaining why, but thank you.

Rip

 

[Guest]

I agree with Ken that there are many advantages to breaking up the components
of the field, but aside from that I think that you will actually need an
append query to do what you want, rather than an update query. An update
query will only change existing records (for instance you could change all
housing unit numbers from 1 to 2), but it will not add records. If you want
to add records for each housing unit based on your existing records for
housing unit one, you could use a series of append queries, such as:

INSERT INTO [YourTableName] ([ BunkNumberFieldName] )
SELECT "2" & Right([BunkNumberFieldName],Len([BunkNumberFieldName])-1) AS
NewBunkNo
FROM [YourTableName]
WHERE Left([BunkNumberFieldName],1)="1";

Note that the query uses a where condition to only base the append query on
the units starting with one because you will need it to avoid duplicates
after the first append query is run (since Units 1 and 2 would both exist at
that point).

To do your updates, you will need to change the "2" following the SELECT
statement to the new housing unit number for each run. Also, you will need
to replace [YourTableName] with your actual table name, and
[BunkNumberFieldName] with your field name. You can get rid of the square
brackets if your table and field name do not include spaces.

If you wish, you could replace the "2" following the SELECT statement with a
parameter so that the query would just prompt you for the new unit number to
be added each time.

HTH, Ted Allen

 

[Guest]

Outstanding. You have definitely helped.

Thanx so much,

Rip

 

[Ken Snell [MVP]]

OK - I see that you have found a solution with append query, which is the
correct approach. Good luck.