Update

"Dmac" wrote in
:

I am wondering if anyone can give me some ideas on how to achieve
this task.

I have a table with 22 rows. There are approximately 15 fields in
each row, which contains information about a particular person.
One of the fields in txtRoomNumber. On occassion that person may
changes rooms, ie. from room 1 to room 10.

Is there a solution where I can update row 10 with the information
from row 1?

I sure would appreciate your feedback.

Thanks.

In a properly normalized database, the only info you need to change
is the person's room number, because all the information that
depends only on hte room number is in a second table, related to the
persons table by that room number. Chang the value of room number,
and the room related info automatically follows.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

I am wondering if anyone can give me some ideas on how to achieve this task.

I have a table with 22 rows. There are approximately 15 fields in each row,
which contains information about a particular person. One of the fields in
txtRoomNumber. On occassion that person may changes rooms, ie. from room 1
to room 10.

Is there a solution where I can update row 10 with the information from row
1?

I sure would appreciate your feedback.

Thanks.

Thanks for your response Bob.

Being a newbie, I have a couple of concerns.

I have a main table called tblIntakeExit. There is another table called
tblMovement which has a relationship to the tblIntakeExit table. This
tracks all the people (prisoners) entering and exiting a particular unit.
the tblCountboard is the table that has the set number of rooms. 1 - 22. I
am using a combo box to select the prisoner number to add the prisoner to
the countboard. I have other text fields on the frmCountboard which
references some of the other information using the =PNumber.Column(2)
property.

The problem I am having is that sometimes a prisoner has to be moved to
another cell/room. If I set it up as you suggest, then I would have two
cells in the table that are the same when a move was made, unless whoever is
making the move remembers to change the other room to the now vacant room.

Normally I would just print the rptcountboard report showing who is
currently living in the unit. The problem I have is that I need to show the
rooms that are also vacant. Maybe I am just not getting this and need some
further direction. I actually feel stupid about this.

I sure do appreciate your opinion and expertise. Thanks in advance.


"Bob Quintal" wrote in message
...
"Dmac" wrote in
:

I am wondering if anyone can give me some ideas on how to achieve
this task.

I have a table with 22 rows. There are approximately 15 fields in
each row, which contains information about a particular person.
One of the fields in txtRoomNumber. On occassion that person may
changes rooms, ie. from room 1 to room 10.

Is there a solution where I can update row 10 with the information
from row 1?

I sure would appreciate your feedback.

Thanks.

In a properly normalized database, the only info you need to change
is the person's room number, because all the information that
depends only on hte room number is in a second table, related to the
persons table by that room number. Chang the value of room number,
and the room related info automatically follows.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

"Dmac" wrote in
:

Thanks for your response Bob.

Being a newbie, I have a couple of concerns.

I have a main table called tblIntakeExit. There is another table
called tblMovement which has a relationship to the tblIntakeExit
table. This tracks all the people (prisoners) entering and
exiting a particular unit. the tblCountboard is the table that has
the set number of rooms. 1 - 22. I am using a combo box to
select the prisoner number to add the prisoner to the countboard.
I have other text fields on the frmCountboard which references
some of the other information using the =PNumber.Column(2)
property.

The problem I am having is that sometimes a prisoner has to be
moved to another cell/room. If I set it up as you suggest, then I
would have two cells in the table that are the same when a move
was made, unless whoever is making the move remembers to change
the other room to the now vacant room.

Ok, so what you need is to erase the prisoner number from any other
rooms he may be shown as occupying.

The fix is to make the occupant column in the tblCountboard unique.
If someone tries to put somebody into a second room, Access will
say, can't do that!, forcing the person entering the data to find
the othre room, clear the person from that room, and then go back
and enter them in the new room.

But that kind of fix irritates people doing data entry, the first
few dozen times, then they learn.

But you can automate the checking and clearing of code with a little
bit of programming in the form.

in the combobox Before_Update event, clear the occupant from the
table if the ID is there. Something like:
Docmd.RunSQL "Update tblCountBoard SET Occupant = null WHERE
occupant = " & me.comboboxName
But you'll need to change the field and control names.

Normally I would just print the rptcountboard report showing who
is currently living in the unit. The problem I have is that I
need to show the rooms that are also vacant. Maybe I am just not
getting this and need some further direction. I actually feel
stupid about this.

If you change the query for the report to show all rows from
tblCountBoard and only records matrching... from the other table,
you're almost there. You change the relationship by double-clicking
on the join line in the query builder.

To put the word VACANT in the control box if there is no occupant,
just use IsNull(value, "Alternate if value is null")


I sure do appreciate your opinion and expertise. Thanks in
advance.


"Bob Quintal" wrote in message
...
"Dmac" wrote in
:

I am wondering if anyone can give me some ideas on how to
achieve this task.

I have a table with 22 rows. There are approximately 15 fields
in each row, which contains information about a particular
person. One of the fields in txtRoomNumber. On occassion that
person may changes rooms, ie. from room 1 to room 10.

Is there a solution where I can update row 10 with the
information from row 1?

I sure would appreciate your feedback.

Thanks.

In a properly normalized database, the only info you need to
change is the person's room number, because all the information
that depends only on hte room number is in a second table,
related to the persons table by that room number. Chang the value
of room number, and the room related info automatically follows.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com






--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com