Table(s) layout

[NeilH]

Hi All

I am pretty new to databases and would really appreciate some help.
The problem is that im trying to create a database that will give the
names of pupils at my school with relation to the subjects that they
are studying.

e.g. Show everyone that is taking Biology and Geography,
Show everyone that is taking a science etc

I thought of having tables that look like this

LanguagesTbl
ID Language
1 English
2 French
3 German

ScienceTbl
ID Science
1 Physics
2 Biology
3 Chemistry

PupilTbl
ID Surname Forname etc.................
1 Smith John

Im having a problem trying to think how i can connect each pupil to
their subject and still allow flexabililty if new subjects were added
to the database. ie Do i put the ID numbers of each subject the pupils
are taking into the PupilTbl (would this not make the table huge, as
it has to take all variations into account)

Can anyone shed some light?

Many Thanks

Neil

[Larry Linson]

You need a Courses table, not separate tables for categories of studies. If
you need to categorize the Courses, you can create a Categories table to
join with the Courses in order to select. Then, because you have a many to
many relationship between the Students and Courses tables, you'll need a
"junction" or "intersection" table that has foreign key fields pointing to
the Students table and the Courses table.

Getting the information you want, then, will be a relatively simple matter
of using the Query Builder to create Queries.

I caution you, if you use the Table (or table name) as though it were data,
as you have in your example, you will pay the price later in difficulty
Querying and handling the information in your database application.

Also, the most common naming convention used with Access is the Reddick
naming convention, a "descendent" of the Leszynski-Reddick naming convention
introduced back in the early days of Access 1.1 or 2.0. That includes
prefixes that indicate the type of object (where you used suffixes; some
will tell you that it really doesn't matter what convention you use as long
as you are consistent. My experience indicates that it does matter, if you
exchange your work with others, or discuss it here in the newsgroup -- if
you use the commonly-used naming system, that communication will be
enhanced. You'll find it at
http://www.triadconsulting.com/Resources/Reddick.htm.

Larry Linson
Microsoft Access MVP

"NeilH" <ne**********@ntlworld.com> wrote in message
news:75*************************@posting.google.co m...

Hi All

I am pretty new to databases and would really appreciate some help.
The problem is that im trying to create a database that will give the
names of pupils at my school with relation to the subjects that they
are studying.

e.g. Show everyone that is taking Biology and Geography,
Show everyone that is taking a science etc

I thought of having tables that look like this

LanguagesTbl
ID Language
1 English
2 French
3 German

ScienceTbl
ID Science
1 Physics
2 Biology
3 Chemistry

PupilTbl
ID Surname Forname etc.................
1 Smith John

Im having a problem trying to think how i can connect each pupil to
their subject and still allow flexabililty if new subjects were added
to the database. ie Do i put the ID numbers of each subject the pupils
are taking into the PupilTbl (would this not make the table huge, as
it has to take all variations into account)

Can anyone shed some light?

Many Thanks

Neil

[NeilH]

Thanks for your help

After your advice, I am thinking of having tables that look like this

CoursesTbl
CourseID Course
1 Chemistry
2 Physics
3 French
4 English
5 History
etc etc

CategoryTbl
CategoryID Category
1 Science
2 Languages
3 Humanities
etc etc

CourseCategortyTbl
CourseID CategoryID
1 1
2 1
3 2
4 2
5 3

I am still struggling try to work out how i can assign several course
ID's to one student

PupilTbl
PupilID Surname Forname CourseID?
1 Smith John need to have several

How would i have a varying amount of categories without making the
table have alot of columns

many thanks again
Neil
"Larry Linson" <bo*****@localhost.not> wrote in message news:<8e***********@nwrddc02.gnilink.net>...

You need a Courses table, not separate tables for categories of studies. If
you need to categorize the Courses, you can create a Categories table to
join with the Courses in order to select. Then, because you have a many to
many relationship between the Students and Courses tables, you'll need a
"junction" or "intersection" table that has foreign key fields pointing to
the Students table and the Courses table.

Getting the information you want, then, will be a relatively simple matter
of using the Query Builder to create Queries.

I caution you, if you use the Table (or table name) as though it were data,
as you have in your example, you will pay the price later in difficulty
Querying and handling the information in your database application.

Also, the most common naming convention used with Access is the Reddick
naming convention, a "descendent" of the Leszynski-Reddick naming convention
introduced back in the early days of Access 1.1 or 2.0. That includes
prefixes that indicate the type of object (where you used suffixes; some
will tell you that it really doesn't matter what convention you use as long
as you are consistent. My experience indicates that it does matter, if you
exchange your work with others, or discuss it here in the newsgroup -- if
you use the commonly-used naming system, that communication will be
enhanced. You'll find it at

[Roger]

tblCourse
courseId autoNumber (pk)
course text
categoryId long integer (fk)

tblCategory
categoryId autoNumber (pk)
category text

tblPupil
pupilId autoNumber (pk)
firstName text
lastName text
tblPupilCourse
pupilId long integer (fk)
courseId long integer (fk)

ne**********@ntlworld.com (NeilH) wrote in message news:<75**************************@posting.google. com>...

Thanks for your help

After your advice, I am thinking of having tables that look like this

CoursesTbl
CourseID Course
1 Chemistry
2 Physics
3 French
4 English
5 History
etc etc

CategoryTbl
CategoryID Category
1 Science
2 Languages
3 Humanities
etc etc

CourseCategortyTbl
CourseID CategoryID
1 1
2 1
3 2
4 2
5 3

I am still struggling try to work out how i can assign several course
ID's to one student

PupilTbl
PupilID Surname Forname CourseID?
1 Smith John need to have several

How would i have a varying amount of categories without making the
table have alot of columns

many thanks again
Neil
"Larry Linson" <bo*****@localhost.not> wrote in message news:<8e***********@nwrddc02.gnilink.net>...

You need a Courses table, not separate tables for categories of studies. If
you need to categorize the Courses, you can create a Categories table to
join with the Courses in order to select. Then, because you have a many to
many relationship between the Students and Courses tables, you'll need a
"junction" or "intersection" table that has foreign key fields pointing to
the Students table and the Courses table.

Getting the information you want, then, will be a relatively simple matter
of using the Query Builder to create Queries.

I caution you, if you use the Table (or table name) as though it were data,
as you have in your example, you will pay the price later in difficulty
Querying and handling the information in your database application.

Also, the most common naming convention used with Access is the Reddick
naming convention, a "descendent" of the Leszynski-Reddick naming convention
introduced back in the early days of Access 1.1 or 2.0. That includes
prefixes that indicate the type of object (where you used suffixes; some
will tell you that it really doesn't matter what convention you use as long
as you are consistent. My experience indicates that it does matter, if you
exchange your work with others, or discuss it here in the newsgroup -- if
you use the commonly-used naming system, that communication will be
enhanced. You'll find it at