Multivalued Lookup Column - Only Shows First Value?

[midgimonk]

Hello,

This is my first post, so if my question sounds all jacked up, please let me
know.

Background:
Multiple Buildings with varying amount of Rooms and Students to fill those
rooms.

Table1 Building
Table2 Room
Table3 Student

Table1 Building has building info (not to concerned with this).

Table2 Room has occupancy Information:

RoomNumber (1A,1B; 2A, 2B; ect - some rooms have 2 beds per room - some have
1)
Student (LookUp Column from Table3 "Student." Used the wizard which created
a multivalued query - StudentLast, StudentFirst, StudentSSN - no problem so
far)

Problem I am having is that when I choose the multiple value for the
"Student" column in Table2 Room, it will display all the desired information
from Table3 Student as long as the ComboBox has focus. Once the ComboBox
loses focus, the only value from the multiple value query to display in the
"Student" column in Table2 Room is the first value, "StudentLast."

Is there a way that I can get all of the values to display in the LookUp
Column "Student" in Table2 Room.

Better yet, is there a way that I can have a multiple value LookUp column
from Table2 Room named StudentLast. Once a value from StudentLast is chosen,
it will display the StudentLast from Table3 Student (already there), and then
have other columns in Table2 Room filled in automatically (ie StudentFirst,
StudentSSN) uniquely identified with information from Table3 Student?

I know, it sounds confusing. I tried. Please tell me how I can simply the
question.

 

[midgimonk]

Forgot to include the fact that I'm using Access 2003

 

[tina]

comments inline.

Hello,

This is my first post, so if my question sounds all jacked up, please let me
know.

Background:
Multiple Buildings with varying amount of Rooms and Students to fill those
rooms.

Table1 Building
Table2 Room
Table3 Student

Table1 Building has building info (not to concerned with this).

hmm, okay so far, i guess. just make sure that any fields in this table
describe characteristics of a *building*, not any characteristics of a room
or a student.

Table2 Room has occupancy Information:

RoomNumber (1A,1B; 2A, 2B; ect - some rooms have 2 beds per
room - some have 1) Student (LookUp Column from Table3 "Student."
Used the wizard which created a multivalued query -
StudentLast, StudentFirst, StudentSSN - no problem so far)

okay, stop here. first, get rid of the Lookup field in the Rooms table. for
more information, see http://home.att.net/~california.db/tips.html#aTip8. to
get rid of the Lookup, open the table in Design view, click on the field
name, in Field Properties at the bottom of the screen, click on the Lookup
tab, and change Display Control to Text Box.

next, remember that each field in the Rooms table describes a characteristic
of a room - and *nothing about students*. and each field holds atomic data;
for instance, a room number would be 1, or 2, or 3, etc - not 1A, 1B. the A
and B indicate how many beds are in a room, so that data belongs in its' own
field. and, student data does not belong in this table, so you should remove
the Student field entirely. an example table would be

tblRooms
RoomID (primary key, autonumber)
RoomNumber
BuildingID (foreign key from tblBuildings)
<this field identifies which building the room is associated with>
<note: you'll probably want to put a unique multi-field index on
RoomNumber/BuildingID, to ensure that each room in each building is listed
only once>
BedCount (unless all rooms will *always* have the same number of beds,
forever, or unless you don't care about tracking bed count.)

Problem I am having is that when I choose the multiple value for the
"Student" column in Table2 Room, it will display all the desired information
from Table3 Student as long as the ComboBox has focus. Once the ComboBox
loses focus, the only value from the multiple value query to display in the
"Student" column in Table2 Room is the first value, "StudentLast."

Is there a way that I can get all of the values to display in the LookUp
Column "Student" in Table2 Room.

Better yet, is there a way that I can have a multiple value LookUp column
from Table2 Room named StudentLast. Once a value from StudentLast is chosen,
it will display the StudentLast from Table3 Student (already there), and then
have other columns in Table2 Room filled in automatically (ie StudentFirst,
StudentSSN) uniquely identified with information from Table3 Student?

no, you don't want to do any of this. refer to my comments above. you have a
one-to-many relationship between rooms and students: one room may house
multiple students, but each student is housed in one room.

now, if students may be moved from one room to another AND if you want a
historical record of that movement, then you need a Students table that does
*not* include room assignment, and a linking table to relate Student records
to Room records. otherwise, you just need a Students table, which includes a
RoomID field to link it to tblRooms, as

tblStudents
StudentID (primary key)
FirstName
MI
LastName
RoomID (foreign key from tblRooms)
<other fields that describe a student>

recommend you read up on relational design principles; getting the
tables/relationships structures correct is vital, it's the bedrock of your
database design, and now is the time to do it right - before you start
building queries, forms and reports on top of the bedrock. for more
information, see http://home.att.net/~california.db/tips.html#aTip1.

hth

 

[midgimonk via AccessMonster.com]

Let me give a little reason of why I did Student LookUp in Rooms. I
understand about "Atomic Data" as you put it, and that a table named Student
would only house info about Students, Room about Rooms, and so on.

My attempt here is to prevent Data Duplication. I am trying to "automate"
the insertion of Students into a perspective room, or better yet, bed space.
One student one bed. A student can't be inserted into a room unless they
have been added to the Student Table.

With that being said, why wouldn't I want to have a LookUp Table. I
understand the "black box" concept of Tables, but I really don't want to have
to hand jam in 3500 + Students, especially when they are already in another
table named Student?

More guidance requested!

Respectfully,
MidgiMonk

comments inline.

Hello,

[quoted text clipped - 10 lines]

Table1 Building has building info (not to concerned with this).

hmm, okay so far, i guess. just make sure that any fields in this table
describe characteristics of a *building*, not any characteristics of a room
or a student.

Table2 Room has occupancy Information:

RoomNumber (1A,1B; 2A, 2B; ect - some rooms have 2 beds per
room - some have 1) Student (LookUp Column from Table3 "Student."
Used the wizard which created a multivalued query -
StudentLast, StudentFirst, StudentSSN - no problem so far)

okay, stop here. first, get rid of the Lookup field in the Rooms table. for
more information, see http://home.att.net/~california.db/tips.html#aTip8. to
get rid of the Lookup, open the table in Design view, click on the field
name, in Field Properties at the bottom of the screen, click on the Lookup
tab, and change Display Control to Text Box.

next, remember that each field in the Rooms table describes a characteristic
of a room - and *nothing about students*. and each field holds atomic data;
for instance, a room number would be 1, or 2, or 3, etc - not 1A, 1B. the A
and B indicate how many beds are in a room, so that data belongs in its' own
field. and, student data does not belong in this table, so you should remove
the Student field entirely. an example table would be

tblRooms
RoomID (primary key, autonumber)
RoomNumber
BuildingID (foreign key from tblBuildings)
<this field identifies which building the room is associated with>
<note: you'll probably want to put a unique multi-field index on
RoomNumber/BuildingID, to ensure that each room in each building is listed
only once>
BedCount (unless all rooms will *always* have the same number of beds,
forever, or unless you don't care about tracking bed count.)

Problem I am having is that when I choose the multiple value for the
"Student" column in Table2 Room, it will display all the desired information

[quoted text clipped - 10 lines]

have other columns in Table2 Room filled in automatically (ie StudentFirst,
StudentSSN) uniquely identified with information from Table3 Student?

no, you don't want to do any of this. refer to my comments above. you have a
one-to-many relationship between rooms and students: one room may house
multiple students, but each student is housed in one room.

now, if students may be moved from one room to another AND if you want a
historical record of that movement, then you need a Students table that does
*not* include room assignment, and a linking table to relate Student records
to Room records. otherwise, you just need a Students table, which includes a
RoomID field to link it to tblRooms, as

tblStudents
StudentID (primary key)
FirstName
MI
LastName
RoomID (foreign key from tblRooms)
<other fields that describe a student>

recommend you read up on relational design principles; getting the
tables/relationships structures correct is vital, it's the bedrock of your
database design, and now is the time to do it right - before you start
building queries, forms and reports on top of the bedrock. for more
information, see http://home.att.net/~california.db/tips.html#aTip1.

hth

I know, it sounds confusing. I tried. Please tell me how I can simply the
question.

 

[tina]

comments inline.

Let me give a little reason of why I did Student LookUp in Rooms. I
understand about "Atomic Data" as you put it, and that a table named Student
would only house info about Students, Room about Rooms, and so on.

My attempt here is to prevent Data Duplication. I am trying to "automate"
the insertion of Students into a perspective room, or better yet, bed space.
One student one bed. A student can't be inserted into a room unless they
have been added to the Student Table.

With that being said, why wouldn't I want to have a LookUp Table. I
understand the "black box" concept of Tables, but I really don't want to have
to hand jam in 3500 + Students, especially when they are already in another
table named Student?

okay, i'm afraid you completely lost me. i've never heard of a "black box"
concept of tables, so you're ahead of me there. and i didn't suggest
entering 3500+ students into a second table when they're already in a
Students table. i also didn't say you shouldn't have a lookup *table*; i
strongly recommended that you not have any Lookup *field* in any table, and
i gave you a website reference to explain the reasons for that
recommendation in some detail.

my suggestion was that you designate a room (bed) for each student, directly
in the Students table, or in a linking table if a history of bed assignments
is required. you could do it the other way around - but you'd need a child
table to uniquely identify each bed, related to the Rooms table, with a
field in the child table to hold the foreign key from the Students table.
or, again, if you need historical data, you'd need a linking table between
the child "beds" table and the Students table.

my best recommendation is still that you read up/more on relational design
principles.

hth

More guidance requested!

Respectfully,
MidgiMonk

comments inline.

Hello,

[quoted text clipped - 10 lines]

Table1 Building has building info (not to concerned with this).

hmm, okay so far, i guess. just make sure that any fields in this table
describe characteristics of a *building*, not any characteristics of a room
or a student.

Table2 Room has occupancy Information:

RoomNumber (1A,1B; 2A, 2B; ect - some rooms have 2 beds per
room - some have 1) Student (LookUp Column from Table3 "Student."
Used the wizard which created a multivalued query -
StudentLast, StudentFirst, StudentSSN - no problem so far)

okay, stop here. first, get rid of the Lookup field in the Rooms table. for
more information, see http://home.att.net/~california.db/tips.html#aTip8. to
get rid of the Lookup, open the table in Design view, click on the field
name, in Field Properties at the bottom of the screen, click on the Lookup
tab, and change Display Control to Text Box.

next, remember that each field in the Rooms table describes a characteristic
of a room - and *nothing about students*. and each field holds atomic data;
for instance, a room number would be 1, or 2, or 3, etc - not 1A, 1B. the A
and B indicate how many beds are in a room, so that data belongs in its' own
field. and, student data does not belong in this table, so you should remove
the Student field entirely. an example table would be

tblRooms
RoomID (primary key, autonumber)
RoomNumber
BuildingID (foreign key from tblBuildings)
<this field identifies which building the room is associated with>
<note: you'll probably want to put a unique multi-field index on
RoomNumber/BuildingID, to ensure that each room in each building is listed
only once>
BedCount (unless all rooms will *always* have the same number of beds,
forever, or unless you don't care about tracking bed count.)

Problem I am having is that when I choose the multiple value for the
"Student" column in Table2 Room, it will display all the desired

information
[quoted text clipped - 10 lines]

have other columns in Table2 Room filled in automatically (ie StudentFirst,
StudentSSN) uniquely identified with information from Table3 Student?

no, you don't want to do any of this. refer to my comments above. you have a
one-to-many relationship between rooms and students: one room may house
multiple students, but each student is housed in one room.

now, if students may be moved from one room to another AND if you want a
historical record of that movement, then you need a Students table that does
*not* include room assignment, and a linking table to relate Student records
to Room records. otherwise, you just need a Students table, which includes a
RoomID field to link it to tblRooms, as

tblStudents
StudentID (primary key)
FirstName
MI
LastName
RoomID (foreign key from tblRooms)
<other fields that describe a student>

recommend you read up on relational design principles; getting the
tables/relationships structures correct is vital, it's the bedrock of your
database design, and now is the time to do it right - before you start
building queries, forms and reports on top of the bedrock. for more
information, see http://home.att.net/~california.db/tips.html#aTip1.

hth

I know, it sounds confusing. I tried. Please tell me how I can simply the
question.