How do I establish a Many-to-Many Relationship

[jmcarroll7]

I have 2 tables. One contains information about students and the other
contains information about programs that the students attend. I have the
program table/form set up so that multiple students can attend multiple
programs with multiple service codes on only one date.

My Programs Table & Form are set up as follows:
Program ID (Primary Key)
Program Title (Multiple)
Staff Member (Single)
Date(Single)
Student ID (Multiple - a look up field from the "Students" table)
Service Code (Multiple)
Location (Single)
Contact Type (Single)
Contact Time (Single)

My problem occurs when I am trying to run reports and Queries regarding
program attendance.

Programs
Location
Students Date

Students
Program Date
Service Code

Is there a better way to set my tables up? I have read about a 3rd table to
settle the many-to-many issue, but I am very unclear as to how I should set
it up.

Suggestions are GREATLY appreciated.

 

[NetworkTrade]

you need a 3rd cross reference table - or 'toggle' table - that just has
key1:key2 fields - the very crude diagram below possibly helps...:

Table1 - NewTable3 - Table2
Many:key1 - key1:key2 - key2:Many

 

[BruceM]

To add to what has already been written, you need a Students table, a
Courses table, and a junction table (I'll call it tblEnrollment). Here is
an outline:

tblStudent
StudentID (primary key, or PK)
Name, etc.

tblCourse
CourseID (PK)
Description, etc.

tblEnrollment
EnrollmentID (PK)
StudentID (foreign key, or FK)
CourseID (FK)
Comments, or anything specific to a particular student in a particular
course

Establish one-to-many relationships between the PKs and their namesake FKs
in tblEnrollment. It may not be absolutely necessary to have a PK in
tblEnrollment, but I happen to think it's a good idea in case it is needed
in the future. Note that the junction table (tblEnrollment) does not need
to be limited to just the FK fields. For instance, if you need a place to
put a comment such as "Prerequisite course requirement waived" or some such,
that's where it would go.

Typically you would make a form based on tblStudent, with a subform based on
tblEnrollment. The subform would have a combo box that has tblCourse as its
row source. The combo box is bound to CourseID. This will let you view the
student record, and add a listing of courses. If you want to do it the
other way around (a course listing to which student names are added), just
make the main form based on tblCourse. The subform is still based on
tblEnrollment, and the combo box row source is tblStudent. In either case,
you can produce a report that shows the information in another way. That
is, if the main form is based on tblStudent (allowing you to enter course
information for each student), a report can be based on tblCourse, with a
subreport based on tblEnrollment, allowing you to see attendeees for each
course.

 

[jmcarroll7]

O.k. Now I have set up the 3rd Table. My structure is as follows:

Table 1 - Students
Student ID (PK)
Name, etc

Table 2 - Programs
Program Code (PK)
Program Names (each program belongs to one code)

Table 3 - Program Attendance
Contact ID (PK)
Staff Names
Location
Date
Programs (FK)
Contact Type
Time
Student ID (FK)
Notes

I have now set up a form based on Table 3. I have the Programs filed set up
as a list box w/ multi-select enabled, I also have the Students table set up
as a list box w/ multi-select enabled.

I have entered in 3 test Attendance forms. Now neither the students or the
programs I selected show up in the tables or reports.

Please let me know what I am NOW doing incorrectly.

Thank you!

 

[BruceM]

I will continue for the most part to use the names I suggested for the
forms. You may of course use what names you choose.
Your junction table has grown well beyond what I suggested, and I do not
know the purpose of most of your fields. I would think that things such as
location, staff, time, and so forth are information about the program, and
therefore should be in tblPrograms (I called it tblCourses). I would asume
that everybody who attends will attend at the same time and place. If not,
more explanation is needed. What is ContactID? If the idea is that you
have a table containing Staff information, and that you want to select a
staff member when you are creating or modifying the record that identifies a
particular program, then you need a separate Staff table (with the PK field
StaffID) related one-to-many with tblCourse (which needs to contain a field
named StaffID as a foreign key).
Leave Staff out of it for now, and try creating a database using the basic
structure I have suggested. Create the relationships between the PK and FK
fields. Now to make a form (frmStudent) based on tblStudent, and another
form (fsubEnrollment) based on tblEnrollment. Use the wizard to add a combo
box to tblEnrollment. To do this, click the magic wand icon in the toolbox
so that it is highlighted, then click the combo box icon, and draw a combo
box on the form. The wizard will take over. Specify that you want the rows
to come from tblCourses. Add StudentID first, then the name field or
fields, and follow the prompts. You will probably be choosing the default
option in most cases. Hide the StudentID column when you get the chance or
when prompted to do so.
With frmStudent open in design view, drag the icon for fsubEnrollment onto
frmStudent, and arrange it to suit your preference. Switch to form view.
Navigate to a student's record, then try adding a course in the subform.
Remember this about tables: you should be able to describe a table in a
single sentence without using the word "and". Having said that, name AND
address is OK because it is all personal information, but name AND courses
attended is not OK. Courses are attended by several students, so should not
be in a single student's record. That's where the relationships between
tables come in. The junction table contains information about a particular
student's participation in a single program. That's all. If there's
anything else, it needs to go.
Keep it simple, and experiment a bit with the scaled-down version. It can
be expanded later, once you see how the basic structure works.

 

[jmcarroll7]

I am not sure what has happened. I changed the tables to your specs and went
into the Students form and inserted the subformAttendance(Enrollment). I
tried making the combo box as you said, however when I went through the
wizard, studentID was not showing up in the table Programs(Courses). I quit
the wizard and went into the Programs (Courses) Table and in the datasheet
view there is now a subdatasheet based on Attendance. Is this supposed to
happen? How do I fix it?

 

[BruceM]

If you mean that there is a + sign next to each student's record in table
view, you seem to be on track. In the wizard you would have selected
tblCourse as the Row Source for the combo box. You are trying to relate a
course record to a student record.
Let's back up a little. The subform is bound to a table (or query).
Controls on the subform are bound to fields in that table. fsubEnrollment
should contain one combo box, that's all. That combo box will have
ProgramID as its record source.
Create a query based on tblProgram. Add ProgramID and the description
field, nothing else (two columns). Sort ascending (in the query design
grid) in the description field. Save the query and give it a name. In the
combo box properties, click Row Source and select your new query. Also in
Properties, set the Column Count to 2, the Column Widths to 0";1", and the
Bound Column to 1.
The query should serve the same purpose as something like this (instead of
the query) in Row Source:
SELECT [tblProgram].[ProgramID], [tblProgram].[Description] FROM tblProgram
ORDER BY [Description];
Just to be sure it's clear, in order to see the combo box properties, click
the combo box to select it, then click View > Properties.

 

[jmcarroll7]

There is a plus sign next to each student and next to each program in the
Programs Table. Am I still on the right track?

If you mean that there is a + sign next to each student's record in table
view, you seem to be on track. In the wizard you would have selected
tblCourse as the Row Source for the combo box. You are trying to relate a
course record to a student record.
Let's back up a little. The subform is bound to a table (or query).
Controls on the subform are bound to fields in that table. fsubEnrollment
should contain one combo box, that's all. That combo box will have
ProgramID as its record source.
Create a query based on tblProgram. Add ProgramID and the description
field, nothing else (two columns). Sort ascending (in the query design
grid) in the description field. Save the query and give it a name. In the
combo box properties, click Row Source and select your new query. Also in
Properties, set the Column Count to 2, the Column Widths to 0";1", and the
Bound Column to 1.
The query should serve the same purpose as something like this (instead of
the query) in Row Source:
SELECT [tblProgram].[ProgramID], [tblProgram].[Description] FROM tblProgram
ORDER BY [Description];
Just to be sure it's clear, in order to see the combo box properties, click
the combo box to select it, then click View > Properties.

I am not sure what has happened. I changed the tables to your specs and
went
into the Students form and inserted the subformAttendance(Enrollment). I
tried making the combo box as you said, however when I went through the
wizard, studentID was not showing up in the table Programs(Courses). I
quit
the wizard and went into the Programs (Courses) Table and in the datasheet
view there is now a subdatasheet based on Attendance. Is this supposed to
happen? How do I fix it?

 

[BruceM]

Yes. When there is a one-to-many relationship between fields, the records
in the table on the "one" side of the relationship will show the + sign if
that option (available from Access 2000 onward) is enabled. I can't recall
offhand how to enable or disable that option, and I am not familiar with all
of the nuances. When you click on the + sign you will see the related
records (if any). For example, if you click on the + sign next to a
student's name, you will see all of the records from the junction table
(tblEnrollment) that are related to that student's record. That is, you
will see a listing of programs that student has attended or is attending.
Having said that, remember that working directly with the table is something
you do in the development stages. Users should work with forms. There are
sometimes administrative reasons for working directly with tables (adding an
item to a department list, perhaps), but in general you should be working
with forms and reports.
Click Tools > Relationships and verify that the relationships are
one-to-many (you will see a 0 and an infinity sign). If not, click the
relationsip line, then click Relationships > Edit Relationships, then check
the box for Enforce Referential Integrity. It sounds as if you have done
this, but I'll mention it anyhow.
The main form or report is based on a table on the "one" side of the
relationship, and the subform is based on the junction table.
It is worth saying a bit about queries. Then can be used (among other
methods) for sorting records, or for combining fields (FirstName and
LastName can be combined into FullName, for instance), or for setting
criteria (such as viewing records from a particular time period), or for a
number of other reasons. If a form is based on a table, and if you build a
query based on that table, you can use the query as the form's record source
in the same way as you would use the table.

There is a plus sign next to each student and next to each program in the
Programs Table. Am I still on the right track?

If you mean that there is a + sign next to each student's record in table
view, you seem to be on track. In the wizard you would have selected
tblCourse as the Row Source for the combo box. You are trying to relate
a
course record to a student record.
Let's back up a little. The subform is bound to a table (or query).
Controls on the subform are bound to fields in that table.
fsubEnrollment
should contain one combo box, that's all. That combo box will have
ProgramID as its record source.
Create a query based on tblProgram. Add ProgramID and the description
field, nothing else (two columns). Sort ascending (in the query design
grid) in the description field. Save the query and give it a name. In
the
combo box properties, click Row Source and select your new query. Also
in
Properties, set the Column Count to 2, the Column Widths to 0";1", and
the
Bound Column to 1.
The query should serve the same purpose as something like this (instead
of
the query) in Row Source:
SELECT [tblProgram].[ProgramID], [tblProgram].[Description] FROM
tblProgram
ORDER BY [Description];
Just to be sure it's clear, in order to see the combo box properties,
click
the combo box to select it, then click View > Properties.

I am not sure what has happened. I changed the tables to your specs and
went
into the Students form and inserted the subformAttendance(Enrollment).
I
tried making the combo box as you said, however when I went through the
wizard, studentID was not showing up in the table Programs(Courses). I
quit
the wizard and went into the Programs (Courses) Table and in the
datasheet
view there is now a subdatasheet based on Attendance. Is this supposed
to
happen? How do I fix it?

:

I will continue for the most part to use the names I suggested for the
forms. You may of course use what names you choose.
Your junction table has grown well beyond what I suggested, and I do
not
know the purpose of most of your fields. I would think that things
such
as
location, staff, time, and so forth are information about the program,
and
therefore should be in tblPrograms (I called it tblCourses). I would
asume
that everybody who attends will attend at the same time and place. If
not,
more explanation is needed. What is ContactID? If the idea is that
you
have a table containing Staff information, and that you want to select
a
staff member when you are creating or modifying the record that
identifies a
particular program, then you need a separate Staff table (with the PK
field
StaffID) related one-to-many with tblCourse (which needs to contain a
field
named StaffID as a foreign key).
Leave Staff out of it for now, and try creating a database using the
basic
structure I have suggested. Create the relationships between the PK
and
FK
fields. Now to make a form (frmStudent) based on tblStudent, and
another
form (fsubEnrollment) based on tblEnrollment. Use the wizard to add a
combo
box to tblEnrollment. To do this, click the magic wand icon in the
toolbox
so that it is highlighted, then click the combo box icon, and draw a
combo
box on the form. The wizard will take over. Specify that you want
the
rows
to come from tblCourses. Add StudentID first, then the name field or
fields, and follow the prompts. You will probably be choosing the
default
option in most cases. Hide the StudentID column when you get the
chance
or
when prompted to do so.
With frmStudent open in design view, drag the icon for fsubEnrollment
onto
frmStudent, and arrange it to suit your preference. Switch to form
view.
Navigate to a student's record, then try adding a course in the
subform.
Remember this about tables: you should be able to describe a table in
a
single sentence without using the word "and". Having said that, name
AND
address is OK because it is all personal information, but name AND
courses
attended is not OK. Courses are attended by several students, so
should
not
be in a single student's record. That's where the relationships
between
tables come in. The junction table contains information about a
particular
student's participation in a single program. That's all. If there's
anything else, it needs to go.
Keep it simple, and experiment a bit with the scaled-down version. It
can
be expanded later, once you see how the basic structure works.

O.k. Now I have set up the 3rd Table. My structure is as follows:

Table 1 - Students
Student ID (PK)
Name, etc

Table 2 - Programs
Program Code (PK)
Program Names (each program belongs to one code)

Table 3 - Program Attendance
Contact ID (PK)
Staff Names
Location
Date
Programs (FK)
Contact Type
Time
Student ID (FK)
Notes

I have now set up a form based on Table 3. I have the Programs
filed
set
up
as a list box w/ multi-select enabled, I also have the Students
table
set
up
as a list box w/ multi-select enabled.

I have entered in 3 test Attendance forms. Now neither the students
or
the
programs I selected show up in the tables or reports.

Please let me know what I am NOW doing incorrectly.

Thank you!

:

To add to what has already been written, you need a Students table,
a
Courses table, and a junction table (I'll call it tblEnrollment).
Here
is
an outline:

tblStudent
StudentID (primary key, or PK)
Name, etc.

tblCourse
CourseID (PK)
Description, etc.

tblEnrollment
EnrollmentID (PK)
StudentID (foreign key, or FK)
CourseID (FK)
Comments, or anything specific to a particular student in a
particular
course

Establish one-to-many relationships between the PKs and their
namesake
FKs
in tblEnrollment. It may not be absolutely necessary to have a PK
in
tblEnrollment, but I happen to think it's a good idea in case it is
needed
in the future. Note that the junction table (tblEnrollment) does
not
need
to be limited to just the FK fields. For instance, if you need a
place
to
put a comment such as "Prerequisite course requirement waived" or
some
such,
that's where it would go.

Typically you would make a form based on tblStudent, with a subform
based
on
tblEnrollment. The subform would have a combo box that has
tblCourse
as
its
row source. The combo box is bound to CourseID. This will let you
view
the
student record, and add a listing of courses. If you want to do it
the
other way around (a course listing to which student names are
added),
just
make the main form based on tblCourse. The subform is still based
on
tblEnrollment, and the combo box row source is tblStudent. In
either
case,
you can produce a report that shows the information in another way.
That
is, if the main form is based on tblStudent (allowing you to enter
course
information for each student), a report can be based on tblCourse,
with a
subreport based on tblEnrollment, allowing you to see attendeees
for
each
course.

message
I have 2 tables. One contains information about students and the
other
contains information about programs that the students attend. I
have
the
program table/form set up so that multiple students can attend
multiple
programs with multiple service codes on only one date.

My Programs Table & Form are set up as follows:
Program ID (Primary Key)
Program Title (Multiple)
Staff Member (Single)
Date(Single)
Student ID (Multiple - a look up field from the "Students" table)
Service Code (Multiple)
Location (Single)
Contact Type (Single)
Contact Time (Single)

My problem occurs when I am trying to run reports and Queries
regarding
program attendance.

Programs
Location
Students Date

Students
Program Date
Service Code

Is there a better way to set my tables up? I have read about a
3rd
table
to
settle the many-to-many issue, but I am very unclear as to how I
should
set
it up.

Suggestions are GREATLY appreciated.

 

[jmcarroll7]

O.k. - I have checked the relationships and they are as you said. The
students form has an Program Attendance (Enrollment) subform and The Programs
Form has a Program Attendance (Enrollment) subform.

During a single contact, several students can participate in multiple
programs.

However, I am still having a porblem selecting many students to many
programs at the same time. In the sub-form, when I select 1 student and one
program, it is recorded. However, when I use the multi-select tool and
select (for ex.) 2 programs and 4 students who attended those 2 programs it
is not recorded.

Do I need to change the form so that only one student can be selected to
multiple programs or vice versa? Or is there a way to cut down on the entry
time and select multiple students AND multiple programs?

Yes. When there is a one-to-many relationship between fields, the records
in the table on the "one" side of the relationship will show the + sign if
that option (available from Access 2000 onward) is enabled. I can't recall
offhand how to enable or disable that option, and I am not familiar with all
of the nuances. When you click on the + sign you will see the related
records (if any). For example, if you click on the + sign next to a
student's name, you will see all of the records from the junction table
(tblEnrollment) that are related to that student's record. That is, you
will see a listing of programs that student has attended or is attending.
Having said that, remember that working directly with the table is something
you do in the development stages. Users should work with forms. There are
sometimes administrative reasons for working directly with tables (adding an
item to a department list, perhaps), but in general you should be working
with forms and reports.
Click Tools > Relationships and verify that the relationships are
one-to-many (you will see a 0 and an infinity sign). If not, click the
relationsip line, then click Relationships > Edit Relationships, then check
the box for Enforce Referential Integrity. It sounds as if you have done
this, but I'll mention it anyhow.
The main form or report is based on a table on the "one" side of the
relationship, and the subform is based on the junction table.
It is worth saying a bit about queries. Then can be used (among other
methods) for sorting records, or for combining fields (FirstName and
LastName can be combined into FullName, for instance), or for setting
criteria (such as viewing records from a particular time period), or for a
number of other reasons. If a form is based on a table, and if you build a
query based on that table, you can use the query as the form's record source
in the same way as you would use the table.

There is a plus sign next to each student and next to each program in the
Programs Table. Am I still on the right track?

If you mean that there is a + sign next to each student's record in table
view, you seem to be on track. In the wizard you would have selected
tblCourse as the Row Source for the combo box. You are trying to relate
a
course record to a student record.
Let's back up a little. The subform is bound to a table (or query).
Controls on the subform are bound to fields in that table.
fsubEnrollment
should contain one combo box, that's all. That combo box will have
ProgramID as its record source.
Create a query based on tblProgram. Add ProgramID and the description
field, nothing else (two columns). Sort ascending (in the query design
grid) in the description field. Save the query and give it a name. In
the
combo box properties, click Row Source and select your new query. Also
in
Properties, set the Column Count to 2, the Column Widths to 0";1", and
the
Bound Column to 1.
The query should serve the same purpose as something like this (instead
of
the query) in Row Source:
SELECT [tblProgram].[ProgramID], [tblProgram].[Description] FROM
tblProgram
ORDER BY [Description];
Just to be sure it's clear, in order to see the combo box properties,
click
the combo box to select it, then click View > Properties.

I am not sure what has happened. I changed the tables to your specs and
went
into the Students form and inserted the subformAttendance(Enrollment).
I
tried making the combo box as you said, however when I went through the
wizard, studentID was not showing up in the table Programs(Courses). I
quit
the wizard and went into the Programs (Courses) Table and in the
datasheet
view there is now a subdatasheet based on Attendance. Is this supposed
to
happen? How do I fix it?

:

I will continue for the most part to use the names I suggested for the
forms. You may of course use what names you choose.
Your junction table has grown well beyond what I suggested, and I do
not
know the purpose of most of your fields. I would think that things
such
as
location, staff, time, and so forth are information about the program,
and
therefore should be in tblPrograms (I called it tblCourses). I would
asume
that everybody who attends will attend at the same time and place. If
not,
more explanation is needed. What is ContactID? If the idea is that
you
have a table containing Staff information, and that you want to select
a
staff member when you are creating or modifying the record that
identifies a
particular program, then you need a separate Staff table (with the PK
field
StaffID) related one-to-many with tblCourse (which needs to contain a
field
named StaffID as a foreign key).
Leave Staff out of it for now, and try creating a database using the
basic
structure I have suggested. Create the relationships between the PK
and
FK
fields. Now to make a form (frmStudent) based on tblStudent, and
another
form (fsubEnrollment) based on tblEnrollment. Use the wizard to add a
combo
box to tblEnrollment. To do this, click the magic wand icon in the
toolbox
so that it is highlighted, then click the combo box icon, and draw a
combo
box on the form. The wizard will take over. Specify that you want
the
rows
to come from tblCourses. Add StudentID first, then the name field or
fields, and follow the prompts. You will probably be choosing the
default
option in most cases. Hide the StudentID column when you get the
chance
or
when prompted to do so.
With frmStudent open in design view, drag the icon for fsubEnrollment
onto
frmStudent, and arrange it to suit your preference. Switch to form
view.
Navigate to a student's record, then try adding a course in the
subform.
Remember this about tables: you should be able to describe a table in
a
single sentence without using the word "and". Having said that, name
AND
address is OK because it is all personal information, but name AND
courses
attended is not OK. Courses are attended by several students, so
should
not
be in a single student's record. That's where the relationships
between
tables come in. The junction table contains information about a
particular
student's participation in a single program. That's all. If there's
anything else, it needs to go.
Keep it simple, and experiment a bit with the scaled-down version. It
can
be expanded later, once you see how the basic structure works.

O.k. Now I have set up the 3rd Table. My structure is as follows:

Table 1 - Students
Student ID (PK)
Name, etc

Table 2 - Programs
Program Code (PK)
Program Names (each program belongs to one code)

Table 3 - Program Attendance
Contact ID (PK)
Staff Names
Location
Date
Programs (FK)
Contact Type
Time
Student ID (FK)
Notes

I have now set up a form based on Table 3. I have the Programs
filed
set
up
as a list box w/ multi-select enabled, I also have the Students
table
set
up
as a list box w/ multi-select enabled.

I have entered in 3 test Attendance forms. Now neither the students
or
the
programs I selected show up in the tables or reports.

Please let me know what I am NOW doing incorrectly.

Thank you!

:

To add to what has already been written, you need a Students table,
a
Courses table, and a junction table (I'll call it tblEnrollment).
Here
is
an outline:

tblStudent
StudentID (primary key, or PK)
Name, etc.

tblCourse
CourseID (PK)
Description, etc.

tblEnrollment
EnrollmentID (PK)
StudentID (foreign key, or FK)
CourseID (FK)
Comments, or anything specific to a particular student in a
particular
course

Establish one-to-many relationships between the PKs and their
namesake
FKs
in tblEnrollment. It may not be absolutely necessary to have a PK
in
tblEnrollment, but I happen to think it's a good idea in case it is
needed
in the future. Note that the junction table (tblEnrollment) does
not
need
to be limited to just the FK fields. For instance, if you need a
place
to
put a comment such as "Prerequisite course requirement waived" or
some
such,
that's where it would go.

Typically you would make a form based on tblStudent, with a subform
based
on
tblEnrollment. The subform would have a combo box that has
tblCourse
as
its
row source. The combo box is bound to CourseID. This will let you
view
the
student record, and add a listing of courses. If you want to do it
the
other way around (a course listing to which student names are
added),
just
make the main form based on tblCourse. The subform is still based
on
tblEnrollment, and the combo box row source is tblStudent. In
either
case,
you can produce a report that shows the information in another way.
That
is, if the main form is based on tblStudent (allowing you to enter
course
information for each student), a report can be based on tblCourse,
with a
subreport based on tblEnrollment, allowing you to see attendeees
for
each
course.

message
I have 2 tables. One contains information about students and the
other
contains information about programs that the students attend. I
have
the
program table/form set up so that multiple students can attend
multiple
programs with multiple service codes on only one date.

My Programs Table & Form are set up as follows:
Program ID (Primary Key)
Program Title (Multiple)
Staff Member (Single)
Date(Single)
Student ID (Multiple - a look up field from the "Students" table)
Service Code (Multiple)
Location (Single)
Contact Type (Single)
Contact Time (Single)

My problem occurs when I am trying to run reports and Queries
regarding
program attendance.

Programs
Location
Students Date

 

[BruceM]

I don't know what the "multi-select tool" is. If your main form is based on
tblProgram, then you would add students to a program one at a time. Change
the default view of the subform to Continuous if you want to see several
students' names one above the other; otherwise you can use the default
subform navigation buttons to add another student. If your main form is
based on tblStudents, then you can list that student's programs one at a
time. In either case the subform is based on the junction table. Please
specify which approach you are taking.
"During a single contact, several students can participate in multiple
programs" means nothing to me.

O.k. - I have checked the relationships and they are as you said. The
students form has an Program Attendance (Enrollment) subform and The
Programs
Form has a Program Attendance (Enrollment) subform.

During a single contact, several students can participate in multiple
programs.

However, I am still having a porblem selecting many students to many
programs at the same time. In the sub-form, when I select 1 student and
one
program, it is recorded. However, when I use the multi-select tool and
select (for ex.) 2 programs and 4 students who attended those 2 programs
it
is not recorded.

Do I need to change the form so that only one student can be selected to
multiple programs or vice versa? Or is there a way to cut down on the
entry
time and select multiple students AND multiple programs?

Yes. When there is a one-to-many relationship between fields, the
records
in the table on the "one" side of the relationship will show the + sign
if
that option (available from Access 2000 onward) is enabled. I can't
recall
offhand how to enable or disable that option, and I am not familiar with
all
of the nuances. When you click on the + sign you will see the related
records (if any). For example, if you click on the + sign next to a
student's name, you will see all of the records from the junction table
(tblEnrollment) that are related to that student's record. That is, you
will see a listing of programs that student has attended or is attending.
Having said that, remember that working directly with the table is
something
you do in the development stages. Users should work with forms. There
are
sometimes administrative reasons for working directly with tables (adding
an
item to a department list, perhaps), but in general you should be working
with forms and reports.
Click Tools > Relationships and verify that the relationships are
one-to-many (you will see a 0 and an infinity sign). If not, click the
relationsip line, then click Relationships > Edit Relationships, then
check
the box for Enforce Referential Integrity. It sounds as if you have done
this, but I'll mention it anyhow.
The main form or report is based on a table on the "one" side of the
relationship, and the subform is based on the junction table.
It is worth saying a bit about queries. Then can be used (among other
methods) for sorting records, or for combining fields (FirstName and
LastName can be combined into FullName, for instance), or for setting
criteria (such as viewing records from a particular time period), or for
a
number of other reasons. If a form is based on a table, and if you build
a
query based on that table, you can use the query as the form's record
source
in the same way as you would use the table.

There is a plus sign next to each student and next to each program in
the
Programs Table. Am I still on the right track?

:

If you mean that there is a + sign next to each student's record in
table
view, you seem to be on track. In the wizard you would have selected
tblCourse as the Row Source for the combo box. You are trying to
relate
a
course record to a student record.
Let's back up a little. The subform is bound to a table (or query).
Controls on the subform are bound to fields in that table.
fsubEnrollment
should contain one combo box, that's all. That combo box will have
ProgramID as its record source.
Create a query based on tblProgram. Add ProgramID and the description
field, nothing else (two columns). Sort ascending (in the query
design
grid) in the description field. Save the query and give it a name.
In
the
combo box properties, click Row Source and select your new query.
Also
in
Properties, set the Column Count to 2, the Column Widths to 0";1", and
the
Bound Column to 1.
The query should serve the same purpose as something like this
(instead
of
the query) in Row Source:
SELECT [tblProgram].[ProgramID], [tblProgram].[Description] FROM
tblProgram
ORDER BY [Description];
Just to be sure it's clear, in order to see the combo box properties,
click
the combo box to select it, then click View > Properties.

I am not sure what has happened. I changed the tables to your specs
and
went
into the Students form and inserted the
subformAttendance(Enrollment).
I
tried making the combo box as you said, however when I went through
the
wizard, studentID was not showing up in the table Programs(Courses).
I
quit
the wizard and went into the Programs (Courses) Table and in the
datasheet
view there is now a subdatasheet based on Attendance. Is this
supposed
to
happen? How do I fix it?

:

I will continue for the most part to use the names I suggested for
the
forms. You may of course use what names you choose.
Your junction table has grown well beyond what I suggested, and I
do
not
know the purpose of most of your fields. I would think that things
such
as
location, staff, time, and so forth are information about the
program,
and
therefore should be in tblPrograms (I called it tblCourses). I
would
asume
that everybody who attends will attend at the same time and place.
If
not,
more explanation is needed. What is ContactID? If the idea is
that
you
have a table containing Staff information, and that you want to
select
a
staff member when you are creating or modifying the record that
identifies a
particular program, then you need a separate Staff table (with the
PK
field
StaffID) related one-to-many with tblCourse (which needs to contain
a
field
named StaffID as a foreign key).
Leave Staff out of it for now, and try creating a database using
the
basic
structure I have suggested. Create the relationships between the
PK
and
FK
fields. Now to make a form (frmStudent) based on tblStudent, and
another
form (fsubEnrollment) based on tblEnrollment. Use the wizard to
add a
combo
box to tblEnrollment. To do this, click the magic wand icon in the
toolbox
so that it is highlighted, then click the combo box icon, and draw
a
combo
box on the form. The wizard will take over. Specify that you want
the
rows
to come from tblCourses. Add StudentID first, then the name field
or
fields, and follow the prompts. You will probably be choosing the
default
option in most cases. Hide the StudentID column when you get the
chance
or
when prompted to do so.
With frmStudent open in design view, drag the icon for
fsubEnrollment
onto
frmStudent, and arrange it to suit your preference. Switch to form
view.
Navigate to a student's record, then try adding a course in the
subform.
Remember this about tables: you should be able to describe a table
in
a
single sentence without using the word "and". Having said that,
name
AND
address is OK because it is all personal information, but name AND
courses
attended is not OK. Courses are attended by several students, so
should
not
be in a single student's record. That's where the relationships
between
tables come in. The junction table contains information about a
particular
student's participation in a single program. That's all. If
there's
anything else, it needs to go.
Keep it simple, and experiment a bit with the scaled-down version.
It
can
be expanded later, once you see how the basic structure works.

message
O.k. Now I have set up the 3rd Table. My structure is as
follows:

Table 1 - Students
Student ID (PK)
Name, etc

Table 2 - Programs
Program Code (PK)
Program Names (each program belongs to one code)

Table 3 - Program Attendance
Contact ID (PK)
Staff Names
Location
Date
Programs (FK)
Contact Type
Time
Student ID (FK)
Notes

I have now set up a form based on Table 3. I have the Programs
filed
set
up
as a list box w/ multi-select enabled, I also have the Students
table
set
up
as a list box w/ multi-select enabled.

I have entered in 3 test Attendance forms. Now neither the
students
or
the
programs I selected show up in the tables or reports.

Please let me know what I am NOW doing incorrectly.

Thank you!

:

To add to what has already been written, you need a Students
table,
a
Courses table, and a junction table (I'll call it
tblEnrollment).
Here
is
an outline:

tblStudent
StudentID (primary key, or PK)
Name, etc.

tblCourse
CourseID (PK)
Description, etc.

tblEnrollment
EnrollmentID (PK)
StudentID (foreign key, or FK)
CourseID (FK)
Comments, or anything specific to a particular student in a
particular
course

Establish one-to-many relationships between the PKs and their
namesake
FKs
in tblEnrollment. It may not be absolutely necessary to have a
PK
in
tblEnrollment, but I happen to think it's a good idea in case it
is
needed
in the future. Note that the junction table (tblEnrollment)
does
not
need
to be limited to just the FK fields. For instance, if you need
a
place
to
put a comment such as "Prerequisite course requirement waived"
or
some
such,
that's where it would go.

Typically you would make a form based on tblStudent, with a
subform
based
on
tblEnrollment. The subform would have a combo box that has
tblCourse
as
its
row source. The combo box is bound to CourseID. This will let
you
view
the
student record, and add a listing of courses. If you want to do
it
the
other way around (a course listing to which student names are
added),
just
make the main form based on tblCourse. The subform is still
based
on
tblEnrollment, and the combo box row source is tblStudent. In
either
case,
you can produce a report that shows the information in another
way.
That
is, if the main form is based on tblStudent (allowing you to
enter
course
information for each student), a report can be based on
tblCourse,
with a
subreport based on tblEnrollment, allowing you to see attendeees
for
each
course.

message
I have 2 tables. One contains information about students and
the
other
contains information about programs that the students attend.
I
have
the
program table/form set up so that multiple students can attend
multiple
programs with multiple service codes on only one date.

My Programs Table & Form are set up as follows:
Program ID (Primary Key)
Program Title (Multiple)
Staff Member (Single)
Date(Single)
Student ID (Multiple - a look up field from the "Students"
table)
Service Code (Multiple)
Location (Single)
Contact Type (Single)
Contact Time (Single)

My problem occurs when I am trying to run reports and Queries
regarding
program attendance.

Programs
Location
Students Date

 

[jmcarroll7]

In the properties for a list box, under the "All" tab, there is a
"Multi-select" option. You can chose none, simple, or extended. It is
supposed to allow you to choose multiple items from a list.

We are a non-profit working with students. There are approximately 40
different programs/services that are offered on a regular basis. Our centers
have a hard copy form called a "Daily Contact Log" where they document the
students that attended and which programs/services they are engaged in. So
when I say a single contact, I mean the day they attended. One student can
particpate in multiple programs and multiple students can participate in a
program.

I am trying to set the database up so that we can track all of our students
AND track their attendance. We are trying to do this so that we can improve
our reporting methods (for grants, etc) and center our program/service
offerings around the kids needs.

I guess the best way to base the main form on tblPrograms, select a single
program (they usually participate in now more than 3 at a time) and then
select all of the kids that attended.

Is the continous view the way to do this?

Thank you again for all of your help

I don't know what the "multi-select tool" is. If your main form is based on
tblProgram, then you would add students to a program one at a time. Change
the default view of the subform to Continuous if you want to see several
students' names one above the other; otherwise you can use the default
subform navigation buttons to add another student. If your main form is
based on tblStudents, then you can list that student's programs one at a
time. In either case the subform is based on the junction table. Please
specify which approach you are taking.
"During a single contact, several students can participate in multiple
programs" means nothing to me.

O.k. - I have checked the relationships and they are as you said. The
students form has an Program Attendance (Enrollment) subform and The
Programs
Form has a Program Attendance (Enrollment) subform.

During a single contact, several students can participate in multiple
programs.

However, I am still having a porblem selecting many students to many
programs at the same time. In the sub-form, when I select 1 student and
one
program, it is recorded. However, when I use the multi-select tool and
select (for ex.) 2 programs and 4 students who attended those 2 programs
it
is not recorded.

Do I need to change the form so that only one student can be selected to
multiple programs or vice versa? Or is there a way to cut down on the
entry
time and select multiple students AND multiple programs?

Yes. When there is a one-to-many relationship between fields, the
records
in the table on the "one" side of the relationship will show the + sign
if
that option (available from Access 2000 onward) is enabled. I can't
recall
offhand how to enable or disable that option, and I am not familiar with
all
of the nuances. When you click on the + sign you will see the related
records (if any). For example, if you click on the + sign next to a
student's name, you will see all of the records from the junction table
(tblEnrollment) that are related to that student's record. That is, you
will see a listing of programs that student has attended or is attending.
Having said that, remember that working directly with the table is
something
you do in the development stages. Users should work with forms. There
are
sometimes administrative reasons for working directly with tables (adding
an
item to a department list, perhaps), but in general you should be working
with forms and reports.
Click Tools > Relationships and verify that the relationships are
one-to-many (you will see a 0 and an infinity sign). If not, click the
relationsip line, then click Relationships > Edit Relationships, then
check
the box for Enforce Referential Integrity. It sounds as if you have done
this, but I'll mention it anyhow.
The main form or report is based on a table on the "one" side of the
relationship, and the subform is based on the junction table.
It is worth saying a bit about queries. Then can be used (among other
methods) for sorting records, or for combining fields (FirstName and
LastName can be combined into FullName, for instance), or for setting
criteria (such as viewing records from a particular time period), or for
a
number of other reasons. If a form is based on a table, and if you build
a
query based on that table, you can use the query as the form's record
source
in the same way as you would use the table.

There is a plus sign next to each student and next to each program in
the
Programs Table. Am I still on the right track?

:

If you mean that there is a + sign next to each student's record in
table
view, you seem to be on track. In the wizard you would have selected
tblCourse as the Row Source for the combo box. You are trying to
relate
a
course record to a student record.
Let's back up a little. The subform is bound to a table (or query).
Controls on the subform are bound to fields in that table.
fsubEnrollment
should contain one combo box, that's all. That combo box will have
ProgramID as its record source.
Create a query based on tblProgram. Add ProgramID and the description
field, nothing else (two columns). Sort ascending (in the query
design
grid) in the description field. Save the query and give it a name.
In
the
combo box properties, click Row Source and select your new query.
Also
in
Properties, set the Column Count to 2, the Column Widths to 0";1", and
the
Bound Column to 1.
The query should serve the same purpose as something like this
(instead
of
the query) in Row Source:
SELECT [tblProgram].[ProgramID], [tblProgram].[Description] FROM
tblProgram
ORDER BY [Description];
Just to be sure it's clear, in order to see the combo box properties,
click
the combo box to select it, then click View > Properties.

I am not sure what has happened. I changed the tables to your specs
and
went
into the Students form and inserted the
subformAttendance(Enrollment).
I
tried making the combo box as you said, however when I went through
the
wizard, studentID was not showing up in the table Programs(Courses).
I
quit
the wizard and went into the Programs (Courses) Table and in the
datasheet
view there is now a subdatasheet based on Attendance. Is this
supposed
to
happen? How do I fix it?

:

I will continue for the most part to use the names I suggested for
the
forms. You may of course use what names you choose.
Your junction table has grown well beyond what I suggested, and I
do
not
know the purpose of most of your fields. I would think that things
such
as
location, staff, time, and so forth are information about the
program,
and
therefore should be in tblPrograms (I called it tblCourses). I
would
asume
that everybody who attends will attend at the same time and place.
If
not,
more explanation is needed. What is ContactID? If the idea is
that
you
have a table containing Staff information, and that you want to
select
a
staff member when you are creating or modifying the record that
identifies a
particular program, then you need a separate Staff table (with the
PK
field
StaffID) related one-to-many with tblCourse (which needs to contain
a
field
named StaffID as a foreign key).
Leave Staff out of it for now, and try creating a database using
the
basic
structure I have suggested. Create the relationships between the
PK
and
FK
fields. Now to make a form (frmStudent) based on tblStudent, and
another
form (fsubEnrollment) based on tblEnrollment. Use the wizard to
add a
combo
box to tblEnrollment. To do this, click the magic wand icon in the
toolbox
so that it is highlighted, then click the combo box icon, and draw
a
combo
box on the form. The wizard will take over. Specify that you want
the
rows
to come from tblCourses. Add StudentID first, then the name field
or
fields, and follow the prompts. You will probably be choosing the
default
option in most cases. Hide the StudentID column when you get the
chance
or
when prompted to do so.
With frmStudent open in design view, drag the icon for
fsubEnrollment
onto
frmStudent, and arrange it to suit your preference. Switch to form
view.
Navigate to a student's record, then try adding a course in the
subform.
Remember this about tables: you should be able to describe a table
in
a
single sentence without using the word "and". Having said that,
name
AND
address is OK because it is all personal information, but name AND
courses
attended is not OK. Courses are attended by several students, so
should
not
be in a single student's record. That's where the relationships
between
tables come in. The junction table contains information about a
particular
student's participation in a single program. That's all. If
there's
anything else, it needs to go.
Keep it simple, and experiment a bit with the scaled-down version.
It
can
be expanded later, once you see how the basic structure works.

message
O.k. Now I have set up the 3rd Table. My structure is as
follows:

Table 1 - Students
Student ID (PK)
Name, etc

Table 2 - Programs
Program Code (PK)
Program Names (each program belongs to one code)

Table 3 - Program Attendance
Contact ID (PK)
Staff Names
Location
Date
Programs (FK)
Contact Type
Time
Student ID (FK)
Notes

I have now set up a form based on Table 3. I have the Programs
filed
set
up
as a list box w/ multi-select enabled, I also have the Students
table
set
up
as a list box w/ multi-select enabled.

I have entered in 3 test Attendance forms. Now neither the
students
or
the
programs I selected show up in the tables or reports.

Please let me know what I am NOW doing incorrectly.

Thank you!

:

To add to what has already been written, you need a Students
table,
a
Courses table, and a junction table (I'll call it
tblEnrollment).
Here
is
an outline:

tblStudent
StudentID (primary key, or PK)
Name, etc.

tblCourse
CourseID (PK)
Description, etc.

tblEnrollment
EnrollmentID (PK)
StudentID (foreign key, or FK)
CourseID (FK)

 

[jmcarroll7]

Just to add:

All of the students on the log will have participated in the same programs.

In the properties for a list box, under the "All" tab, there is a
"Multi-select" option. You can chose none, simple, or extended. It is
supposed to allow you to choose multiple items from a list.

We are a non-profit working with students. There are approximately 40
different programs/services that are offered on a regular basis. Our centers
have a hard copy form called a "Daily Contact Log" where they document the
students that attended and which programs/services they are engaged in. So
when I say a single contact, I mean the day they attended. One student can
particpate in multiple programs and multiple students can participate in a
program.

I am trying to set the database up so that we can track all of our students
AND track their attendance. We are trying to do this so that we can improve
our reporting methods (for grants, etc) and center our program/service
offerings around the kids needs.

I guess the best way to base the main form on tblPrograms, select a single
program (they usually participate in now more than 3 at a time) and then
select all of the kids that attended.

Is the continous view the way to do this?

Thank you again for all of your help

I don't know what the "multi-select tool" is. If your main form is based on
tblProgram, then you would add students to a program one at a time. Change
the default view of the subform to Continuous if you want to see several
students' names one above the other; otherwise you can use the default
subform navigation buttons to add another student. If your main form is
based on tblStudents, then you can list that student's programs one at a
time. In either case the subform is based on the junction table. Please
specify which approach you are taking.
"During a single contact, several students can participate in multiple
programs" means nothing to me.

O.k. - I have checked the relationships and they are as you said. The
students form has an Program Attendance (Enrollment) subform and The
Programs
Form has a Program Attendance (Enrollment) subform.

During a single contact, several students can participate in multiple
programs.

However, I am still having a porblem selecting many students to many
programs at the same time. In the sub-form, when I select 1 student and
one
program, it is recorded. However, when I use the multi-select tool and
select (for ex.) 2 programs and 4 students who attended those 2 programs
it
is not recorded.

Do I need to change the form so that only one student can be selected to
multiple programs or vice versa? Or is there a way to cut down on the
entry
time and select multiple students AND multiple programs?
:

Yes. When there is a one-to-many relationship between fields, the
records
in the table on the "one" side of the relationship will show the + sign
if
that option (available from Access 2000 onward) is enabled. I can't
recall
offhand how to enable or disable that option, and I am not familiar with
all
of the nuances. When you click on the + sign you will see the related
records (if any). For example, if you click on the + sign next to a
student's name, you will see all of the records from the junction table
(tblEnrollment) that are related to that student's record. That is, you
will see a listing of programs that student has attended or is attending.
Having said that, remember that working directly with the table is
something
you do in the development stages. Users should work with forms. There
are
sometimes administrative reasons for working directly with tables (adding
an
item to a department list, perhaps), but in general you should be working
with forms and reports.
Click Tools > Relationships and verify that the relationships are
one-to-many (you will see a 0 and an infinity sign). If not, click the
relationsip line, then click Relationships > Edit Relationships, then
check
the box for Enforce Referential Integrity. It sounds as if you have done
this, but I'll mention it anyhow.
The main form or report is based on a table on the "one" side of the
relationship, and the subform is based on the junction table.
It is worth saying a bit about queries. Then can be used (among other
methods) for sorting records, or for combining fields (FirstName and
LastName can be combined into FullName, for instance), or for setting
criteria (such as viewing records from a particular time period), or for
a
number of other reasons. If a form is based on a table, and if you build
a
query based on that table, you can use the query as the form's record
source
in the same way as you would use the table.

There is a plus sign next to each student and next to each program in
the
Programs Table. Am I still on the right track?

:

If you mean that there is a + sign next to each student's record in
table
view, you seem to be on track. In the wizard you would have selected
tblCourse as the Row Source for the combo box. You are trying to
relate
a
course record to a student record.
Let's back up a little. The subform is bound to a table (or query).
Controls on the subform are bound to fields in that table.
fsubEnrollment
should contain one combo box, that's all. That combo box will have
ProgramID as its record source.
Create a query based on tblProgram. Add ProgramID and the description
field, nothing else (two columns). Sort ascending (in the query
design
grid) in the description field. Save the query and give it a name.
In
the
combo box properties, click Row Source and select your new query.
Also
in
Properties, set the Column Count to 2, the Column Widths to 0";1", and
the
Bound Column to 1.
The query should serve the same purpose as something like this
(instead
of
the query) in Row Source:
SELECT [tblProgram].[ProgramID], [tblProgram].[Description] FROM
tblProgram
ORDER BY [Description];
Just to be sure it's clear, in order to see the combo box properties,
click
the combo box to select it, then click View > Properties.

I am not sure what has happened. I changed the tables to your specs
and
went
into the Students form and inserted the
subformAttendance(Enrollment).
I
tried making the combo box as you said, however when I went through
the
wizard, studentID was not showing up in the table Programs(Courses).
I
quit
the wizard and went into the Programs (Courses) Table and in the
datasheet
view there is now a subdatasheet based on Attendance. Is this
supposed
to
happen? How do I fix it?

:

I will continue for the most part to use the names I suggested for
the
forms. You may of course use what names you choose.
Your junction table has grown well beyond what I suggested, and I
do
not
know the purpose of most of your fields. I would think that things
such
as
location, staff, time, and so forth are information about the
program,
and
therefore should be in tblPrograms (I called it tblCourses). I
would
asume
that everybody who attends will attend at the same time and place.
If
not,
more explanation is needed. What is ContactID? If the idea is
that
you
have a table containing Staff information, and that you want to
select
a
staff member when you are creating or modifying the record that
identifies a
particular program, then you need a separate Staff table (with the
PK
field
StaffID) related one-to-many with tblCourse (which needs to contain
a
field
named StaffID as a foreign key).
Leave Staff out of it for now, and try creating a database using
the
basic
structure I have suggested. Create the relationships between the
PK
and
FK
fields. Now to make a form (frmStudent) based on tblStudent, and
another
form (fsubEnrollment) based on tblEnrollment. Use the wizard to
add a
combo
box to tblEnrollment. To do this, click the magic wand icon in the
toolbox
so that it is highlighted, then click the combo box icon, and draw
a
combo
box on the form. The wizard will take over. Specify that you want
the
rows
to come from tblCourses. Add StudentID first, then the name field
or
fields, and follow the prompts. You will probably be choosing the
default
option in most cases. Hide the StudentID column when you get the
chance
or
when prompted to do so.
With frmStudent open in design view, drag the icon for
fsubEnrollment
onto
frmStudent, and arrange it to suit your preference. Switch to form
view.
Navigate to a student's record, then try adding a course in the
subform.
Remember this about tables: you should be able to describe a table
in
a
single sentence without using the word "and". Having said that,
name
AND
address is OK because it is all personal information, but name AND
courses
attended is not OK. Courses are attended by several students, so
should
not
be in a single student's record. That's where the relationships
between
tables come in. The junction table contains information about a
particular
student's participation in a single program. That's all. If
there's
anything else, it needs to go.
Keep it simple, and experiment a bit with the scaled-down version.
It
can
be expanded later, once you see how the basic structure works.

message
O.k. Now I have set up the 3rd Table. My structure is as
follows:

Table 1 - Students
Student ID (PK)
Name, etc

Table 2 - Programs
Program Code (PK)
Program Names (each program belongs to one code)

Table 3 - Program Attendance
Contact ID (PK)
Staff Names
Location
Date
Programs (FK)
Contact Type
Time
Student ID (FK)
Notes

I have now set up a form based on Table 3. I have the Programs
filed
set
up
as a list box w/ multi-select enabled, I also have the Students
table
set
up
as a list box w/ multi-select enabled.

I have entered in 3 test Attendance forms. Now neither the
students
or
the
programs I selected show up in the tables or reports.