database structure problem - advice please?

[Johnson]

Here's what I was doing, it is obviously stupid because i have exceeded the
"too many fields defined error"

I have to create a database driven application that allows students to
update the records of which classes they took, what grade they received, and
what semester they took the class in.

I was using one database table, with some unique identifiers for each
student. Then for each of the 50 classes they have to take, I had 4 fields
each, one for whether they took it or not, one for the semester, one for the
year, one for the grade they received.

So all in all I had 4x50 fields for the classes and then the various unique
identifiers.

I just can't fathom how to simplify this problem. Anybody got any ideas?

[Frank]

Re: database structure problem - advice please?

TblStudent
StudentID
<<Other fields you need>>

TblClass
ClassID
<<Other fields you need>>

TblSemester
SemesterID
Semester

TblStudentClass
StudentClassID
StudentID
ClassID
ClassYear
SemesterID
Grade

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
resource@pcdata sheet.com

https://www.pcdatasheet.com

"Johnson" <johnson@spam.t roll> wrote in message
news:0jS_c.1225 $sS4.693@trndny 03...[color=blue]
> Here's what I was doing, it is obviously stupid because i have exceeded[/color]
the[color=blue]
> "too many fields defined error"
>
> I have to create a database driven application that allows students to
> update the records of which classes they took, what grade they received,[/color]
and[color=blue]
> what semester they took the class in.
>
> I was using one database table, with some unique identifiers for each
> student. Then for each of the 50 classes they have to take, I had 4[/color]
fields[color=blue]
> each, one for whether they took it or not, one for the semester, one for[/color]
the[color=blue]
> year, one for the grade they received.
>
> So all in all I had 4x50 fields for the classes and then the various[/color]
unique[color=blue]
> identifiers.
>
> I just can't fathom how to simplify this problem. Anybody got any ideas?
>
>[/color]

[Bernard Peek]

Re: database structure problem - advice please?

In message <0jS_c.1225$sS4 .693@trndny03>, Johnson <johnson@spam.t roll>
writes[color=blue]
>Here's what I was doing, it is obviously stupid because i have exceeded the
>"too many fields defined error"
>
>I have to create a database driven application that allows students to
>update the records of which classes they took, what grade they received, and
>what semester they took the class in.
>
>I was using one database table, with some unique identifiers for each
>student. Then for each of the 50 classes they have to take, I had 4 fields
>each, one for whether they took it or not, one for the semester, one for the
>year, one for the grade they received.
>
>So all in all I had 4x50 fields for the classes and then the various unique
>identifiers.
>
>I just can't fathom how to simplify this problem. Anybody got any ideas?[/color]

It's a classic problem in database design. What you have is information
about three different types of thing, and putting all of that data in
one table is not a good idea. It simplifies database design but can lead
to problems, particularly if you ever need to go back and edit existing
records. Look up "normalizat ion" (or normalisation if you want English
as well as American references.)

You need to split the table. If you normalise the data you find that
there are three different entities; Student, Semester, Class (as Frank
has already pointed out.)

Once you have the data in separate tables you can create a query that
links them. You can create forms and reports based on the query rather
than individual tables. You may find it easier to create one
Student/Semester query and use that on a form then have the Class data
on a subform. Experiment a little and let us know if you hit problems.




--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

[Johnson]

Re: database structure problem - advice please?

Yeah you guys are right. I must have stayed up too late last night because
right now it all seems so clear. I love that breakthrough feeling. The
application is coming along great now. Thanks fellas.


"Bernard Peek" <bap@shrdlu.com > wrote in message
news:9DkB6K0STD PBFwQa@shrdlu.c om...[color=blue]
> In message <0jS_c.1225$sS4 .693@trndny03>, Johnson <johnson@spam.t roll>
> writes[color=green]
> >Here's what I was doing, it is obviously stupid because i have exceeded[/color][/color]
the[color=blue][color=green]
> >"too many fields defined error"
> >
> >I have to create a database driven application that allows students to
> >update the records of which classes they took, what grade they received,[/color][/color]
and[color=blue][color=green]
> >what semester they took the class in.
> >
> >I was using one database table, with some unique identifiers for each
> >student. Then for each of the 50 classes they have to take, I had 4[/color][/color]
fields[color=blue][color=green]
> >each, one for whether they took it or not, one for the semester, one for[/color][/color]
the[color=blue][color=green]
> >year, one for the grade they received.
> >
> >So all in all I had 4x50 fields for the classes and then the various[/color][/color]
unique[color=blue][color=green]
> >identifiers.
> >
> >I just can't fathom how to simplify this problem. Anybody got any ideas?[/color]
>
> It's a classic problem in database design. What you have is information
> about three different types of thing, and putting all of that data in
> one table is not a good idea. It simplifies database design but can lead
> to problems, particularly if you ever need to go back and edit existing
> records. Look up "normalizat ion" (or normalisation if you want English
> as well as American references.)
>
> You need to split the table. If you normalise the data you find that
> there are three different entities; Student, Semester, Class (as Frank
> has already pointed out.)
>
> Once you have the data in separate tables you can create a query that
> links them. You can create forms and reports based on the query rather
> than individual tables. You may find it easier to create one
> Student/Semester query and use that on a form then have the Class data
> on a subform. Experiment a little and let us know if you hit problems.
>
>
>
>
> --
> Bernard Peek
> London, UK. DBA, Manager, Trainer & Author. Will work for money.
>[/color]