Need Designing Help

I'm trying to create a data base where the Record will consist of a form to
input data for a dance title, Included in that form I need to insert the
dance studio that will be performing that dance (that has a drop down menu to
pick from a list of dance studio) and then i need to add the names of
multiple students from a list from that studio only that will be in that
dance only. So i need to be able to chose from a list of students from that
dance studio. And i need to be able to chose multiple students for that
dance....i know i have to use sub-forms but i having a difficult time
designing this.

Studio --
StudioID - autonumber - primary key
Name -
Addr
City
State
ZIP

Student --
StudentID - autonumber - primary key
LName
FName
Phone

DanceSession --
SessionID - autonumber - primary key
StudioID - number - long integer - foreign key
Type - Tango, Waltz, etc.
Day - Mon, Tues, Wed,

StudentSession --
StudentID - number - long integer - foreign key
SessionID - number - long integer - foreign key
Paid - Yes/No

Set one-to-many relationship from primary key to foreign keys.
--
KARL DEWEY
Build a little - Test a little


"Confused" wrote:

I'm trying to create a data base where the Record will consist of a form to
input data for a dance title, Included in that form I need to insert the
dance studio that will be performing that dance (that has a drop down menu to
pick from a list of dance studio) and then i need to add the names of
multiple students from a list from that studio only that will be in that
dance only. So i need to be able to chose from a list of students from that
dance studio. And i need to be able to chose multiple students for that
dance....i know i have to use sub-forms but i having a difficult time
designing this.

Couple of observations, KARL.
You don't show any relation between student and a studio. As it is not
impossible a student could attend more that one studio, perhaps a junction
table to represent that relationship.

Also in the Dance Session table you have Type. There should be a Type table
and the Type field in the Dance Session table should be a foreign key to the
type table.

"KARL DEWEY" wrote in message
...
Studio --
StudioID - autonumber - primary key
Name -
Addr
City
State
ZIP

Student --
StudentID - autonumber - primary key
LName
FName
Phone

DanceSession --
SessionID - autonumber - primary key
StudioID - number - long integer - foreign key
Type - Tango, Waltz, etc.
Day - Mon, Tues, Wed,

StudentSession --
StudentID - number - long integer - foreign key
SessionID - number - long integer - foreign key
Paid - Yes/No

Set one-to-many relationship from primary key to foreign keys.
--
KARL DEWEY
Build a little - Test a little


"Confused" wrote:

I'm trying to create a data base where the Record will consist of a form
to
input data for a dance title, Included in that form I need to insert the
dance studio that will be performing that dance (that has a drop down
menu to
pick from a list of dance studio) and then i need to add the names of
multiple students from a list from that studio only that will be in that
dance only. So i need to be able to chose from a list of students from
that
dance studio. And i need to be able to chose multiple students for that
dance....i know i have to use sub-forms but i having a difficult time
designing this.

I have the student related to Session and then to studio.
--
KARL DEWEY
Build a little - Test a little


"Klatuu" wrote:

Couple of observations, KARL.
You don't show any relation between student and a studio. As it is not
impossible a student could attend more that one studio, perhaps a junction
table to represent that relationship.

Also in the Dance Session table you have Type. There should be a Type table
and the Type field in the Dance Session table should be a foreign key to the
type table.

"KARL DEWEY" wrote in message
...
Studio --
StudioID - autonumber - primary key
Name -
Addr
City
State
ZIP

Student --
StudentID - autonumber - primary key
LName
FName
Phone

DanceSession --
SessionID - autonumber - primary key
StudioID - number - long integer - foreign key
Type - Tango, Waltz, etc.
Day - Mon, Tues, Wed,

StudentSession --
StudentID - number - long integer - foreign key
SessionID - number - long integer - foreign key
Paid - Yes/No

Set one-to-many relationship from primary key to foreign keys.
--
KARL DEWEY
Build a little - Test a little


"Confused" wrote:

I'm trying to create a data base where the Record will consist of a form
to
input data for a dance title, Included in that form I need to insert the
dance studio that will be performing that dance (that has a drop down
menu to
pick from a list of dance studio) and then i need to add the names of
multiple students from a list from that studio only that will be in that
dance only. So i need to be able to chose from a list of students from
that
dance studio. And i need to be able to chose multiple students for that
dance....i know i have to use sub-forms but i having a difficult time
designing this.

I think that is incorrect. A student may be a student at a studio without
being assigned to a session. I would think that when constructing a
session, one might be looking for students from a specific studion.

But, we don't really know the business rules on this, so it could be either
way.


"KARL DEWEY" wrote in message
...
I have the student related to Session and then to studio.
--
KARL DEWEY
Build a little - Test a little


"Klatuu" wrote:

Couple of observations, KARL.
You don't show any relation between student and a studio. As it is not
impossible a student could attend more that one studio, perhaps a
junction
table to represent that relationship.

Also in the Dance Session table you have Type. There should be a Type
table
and the Type field in the Dance Session table should be a foreign key to
the
type table.

"KARL DEWEY" wrote in message
...
Studio --
StudioID - autonumber - primary key
Name -
Addr
City
State
ZIP

Student --
StudentID - autonumber - primary key
LName
FName
Phone

DanceSession --
SessionID - autonumber - primary key
StudioID - number - long integer - foreign key
Type - Tango, Waltz, etc.
Day - Mon, Tues, Wed,

StudentSession --
StudentID - number - long integer - foreign key
SessionID - number - long integer - foreign key
Paid - Yes/No

Set one-to-many relationship from primary key to foreign keys.
--
KARL DEWEY
Build a little - Test a little


"Confused" wrote:

I'm trying to create a data base where the Record will consist of a
form
to
input data for a dance title, Included in that form I need to insert
the
dance studio that will be performing that dance (that has a drop down
menu to
pick from a list of dance studio) and then i need to add the names of
multiple students from a list from that studio only that will be in
that
dance only. So i need to be able to chose from a list of students from
that
dance studio. And i need to be able to chose multiple students for
that
dance....i know i have to use sub-forms but i having a difficult time
designing this.

The user said that a session is set up with students chosen from only one
studio.
What if we add to Karl's design (as I think he intended)

TblStudioStudent
StuStuID (primary key)
StudentID
StudioID


But I'm not sure about the next bit. Instead of having StuStuID as the
foreign key in the StudentSessionTable, how about going with Karl's design
and using this table only to filter the combo which allows the user to only
add students from that studio which appears in TblStudioSession so that
StudentID becomes the foreign key in TblStudentSession

Or would the student's name be enough to identify them?
If we need the Student AND the studio to identify the student, then we would
need to concatenate StudentAnd Studio in our combo and use StuStuID as the
foreign key in the StudentSession table.

Evi


"Klatuu" wrote in message
...
I think that is incorrect. A student may be a student at a studio without
being assigned to a session. I would think that when constructing a
session, one might be looking for students from a specific studion.

But, we don't really know the business rules on this, so it could be
either
way.


"KARL DEWEY" wrote in message
...
I have the student related to Session and then to studio.
--
KARL DEWEY
Build a little - Test a little


"Klatuu" wrote:

Couple of observations, KARL.
You don't show any relation between student and a studio. As it is not
impossible a student could attend more that one studio, perhaps a
junction
table to represent that relationship.

Also in the Dance Session table you have Type. There should be a Type
table
and the Type field in the Dance Session table should be a foreign key
to
the
type table.

"KARL DEWEY" wrote in message
...
Studio --
StudioID - autonumber - primary key
Name -
Addr
City
State
ZIP

Student --
StudentID - autonumber - primary key
LName
FName
Phone

DanceSession --
SessionID - autonumber - primary key
StudioID - number - long integer - foreign key
Type - Tango, Waltz, etc.
Day - Mon, Tues, Wed,

StudentSession --
StudentID - number - long integer - foreign key
SessionID - number - long integer - foreign key
Paid - Yes/No

Set one-to-many relationship from primary key to foreign keys.
--
KARL DEWEY
Build a little - Test a little


"Confused" wrote:

I'm trying to create a data base where the Record will consist of a
form
to
input data for a dance title, Included in that form I need to
insert
the
dance studio that will be performing that dance (that has a drop
down
menu to
pick from a list of dance studio) and then i need to add the names
of
multiple students from a list from that studio only that will be in
that
dance only. So i need to be able to chose from a list of students
from
that
dance studio. And i need to be able to chose multiple students for
that
dance....i know i have to use sub-forms but i having a difficult
time
designing this.

The user said that a session is set up with students chosen from only one
studio

Then there does need to be some way to relate a student to a studio.
If the student can belong to one and only one studio, an foreign key the the
studio table will suffice; however, if a student can be associated with more
than one studio, the junction table I recommened previously will be
necessary.
'
"Evi" wrote in message
...
The user said that a session is set up with students chosen from only one
studio.
What if we add to Karl's design (as I think he intended)

TblStudioStudent
StuStuID (primary key)
StudentID
StudioID


But I'm not sure about the next bit. Instead of having StuStuID as the
foreign key in the StudentSessionTable, how about going with Karl's design
and using this table only to filter the combo which allows the user to
only
add students from that studio which appears in TblStudioSession so that
StudentID becomes the foreign key in TblStudentSession

Or would the student's name be enough to identify them?
If we need the Student AND the studio to identify the student, then we
would
need to concatenate StudentAnd Studio in our combo and use StuStuID as the
foreign key in the StudentSession table.

Evi


"Klatuu" wrote in message
...
I think that is incorrect. A student may be a student at a studio
without
being assigned to a session. I would think that when constructing a
session, one might be looking for students from a specific studion.

But, we don't really know the business rules on this, so it could be
either
way.


"KARL DEWEY" wrote in message
...
I have the student related to Session and then to studio.
--
KARL DEWEY
Build a little - Test a little


"Klatuu" wrote:

Couple of observations, KARL.
You don't show any relation between student and a studio. As it is
not
impossible a student could attend more that one studio, perhaps a
junction
table to represent that relationship.

Also in the Dance Session table you have Type. There should be a Type
table
and the Type field in the Dance Session table should be a foreign key
to
the
type table.

"KARL DEWEY" wrote in message
...
Studio --
StudioID - autonumber - primary key
Name -
Addr
City
State
ZIP

Student --
StudentID - autonumber - primary key
LName
FName
Phone

DanceSession --
SessionID - autonumber - primary key
StudioID - number - long integer - foreign key
Type - Tango, Waltz, etc.
Day - Mon, Tues, Wed,

StudentSession --
StudentID - number - long integer - foreign key
SessionID - number - long integer - foreign key
Paid - Yes/No

Set one-to-many relationship from primary key to foreign keys.
--
KARL DEWEY
Build a little - Test a little


"Confused" wrote:

I'm trying to create a data base where the Record will consist of a
form
to
input data for a dance title, Included in that form I need to
insert
the
dance studio that will be performing that dance (that has a drop
down
menu to
pick from a list of dance studio) and then i need to add the names
of
multiple students from a list from that studio only that will be in
that
dance only. So i need to be able to chose from a list of students
from
that
dance studio. And i need to be able to chose multiple students for
that
dance....i know i have to use sub-forms but i having a difficult
time
designing this.