Issues database template

[vaa571]

Hi, i downloaded the "issues database" from microsoft and made some
modifications. I added a table called "MRB Board Log" and there is a field in
this table "Attendees". The attendees field will keep record os who attended
to this MRB meeting (MRB date) there will be few people at each meeting. i
would like to select them from a drop down or a list. how can i do that? I do
have a form "MRB Log Form".

Thanks

 

[Larry Linson]

Is "Attendees" a multi-value field... that is, do you expect to record
multiple attendees in the one field in the record? If that was your intent
and your design, I strongly suggest you normalize your data and record
Attendees in a related Table, with one Attendee per Record with a Foreign
Key pointing back to the Meeting Record.

Larry Linson
Microsoft Office Access MVP

 

[vaa571]

Thanks Larry. I initial intent was to record multiple attendees in one field
of the record. I like your suggestion but i am not sure how to do that. Can
you please provide example? Thanks again.

 

[Larry Daugherty]

Don't see that you ever got a response to your last - so ...

First, drop the Attendees field from the Meetings record.

What you need is actually a junction table to express the many-to-many
relationship between tblMeeting and tblStudent. You might call that
new table tblMeetingStudent. The Primary Keys of both tblMeeting and
tblStudent will appear in tblMeetingStudent as Long Integer (different
datatypes if you're using Natural Keys) Foreign Keys

tblMeetingStudent
MeetingID (FK) - PK of tblMeeting
StudentID (FK) - PK of tblStudent
... other fields about this student in this meeting

Go to the Relationships window and show the three tables of interest.
Click on tblMeeting.MeetingID and drag to tblMeetingStudent.MeetingID.
DoubleClick on the relationship line and confirm the one to many join
in the correct direction. Turn on Referential Integrity. Repeat for
tblStudent.StudentID.

Create a Form based on tblMeeting. Leave a large space toward the
bottom of the detail section. Read in Help about Form/SubForm and how
to create them. When you drag your subform onto your Form a Subform
Control will be placed on the Form.

When you are designing the Form that will eventually become your
SubForm you should design it to take as little vertical space as
possible. Ideally you'll want a single row of information on it. Do
not display Header and Footer on that form. Set its Display to
"Continuous". *Don't use "Datasheet".

The "**ID" fields should be hidden or eliminated. The first visible
control on the subform record should be a combobox that allows you to
pick from tblStudent.

That may get you moving in the right direction. There will be a lot
of things to experiment with until you are happy with the results.

HTH

 

[Larry Linson]

Don't see that you ever got a response to your last - so ...

First, drop the Attendees field from the Meetings record.

What you need is actually a junction table to express the many-to-many
relationship between tblMeeting and tblStudent. You might call that
new table tblMeetingStudent. The Primary Keys of both tblMeeting and
tblStudent will appear in tblMeetingStudent as Long Integer (different
datatypes if you're using Natural Keys) Foreign Keys

tblMeetingStudent
MeetingID (FK) - PK of tblMeeting
StudentID (FK) - PK of tblStudent
... other fields about this student in this meeting

Go to the Relationships window and show the three tables of interest.
Click on tblMeeting.MeetingID and drag to tblMeetingStudent.MeetingID.
DoubleClick on the relationship line and confirm the one to many join
in the correct direction. Turn on Referential Integrity. Repeat for
tblStudent.StudentID.

Create a Form based on tblMeeting. Leave a large space toward the
bottom of the detail section. Read in Help about Form/SubForm and how
to create them. When you drag your subform onto your Form a Subform
Control will be placed on the Form.

When you are designing the Form that will eventually become your
SubForm you should design it to take as little vertical space as
possible. Ideally you'll want a single row of information on it. Do
not display Header and Footer on that form. Set its Display to
"Continuous". *Don't use "Datasheet".

The "**ID" fields should be hidden or eliminated. The first visible
control on the subform record should be a combobox that allows you to
pick from tblStudent.

That may get you moving in the right direction. There will be a lot
of things to experiment with until you are happy with the results.

Thanks for catching this, Larry... because of other commitments, my
participation in the newsgroups lately has been, put charitably, "spotty".

I agree with your approach... although it might initially seem simpler to
"just have a one-to-many, with students' names repeated as necessary in the
second table, it will be better to have a student table with a "junction
table", and be able to add functionality without having to (later) revise
the storage layout.

Larry Linson
Microsoft Office Access MVP