database table/form design

[Corinne]

I have a database that contains the details of pupils in a school.
What I would like to do may not be possible but I thought I would ask
anyway. Each year the pupils move to a different class, there are 8
classes in each year that are named with the year and the class
teachers initials e.g 5FS would be a year 5 class, teacher Fred Smith.
So every year I have to change the class the pupils are in, thereby
losing the classes they have been in previously. I would like to set
up the form so that I have current class showing on the form header.
The form has tabs that have relevant info (from sub tables/forms) for
each year, so in those places I would like to store the previous class
the pupil was in. Rather than having to type things more than once
would this senario work:

If the current class is 5* then copy to year 5 class in the year 5 tab
page
If the current class is 6* then copy to year 6 class, and so on.
So in the form header I would have a field current class and in each
tab on the form I would have year 5,6,7,8 class that would be copied
from the current class, but when year 5 class changes to year 6 I want
the year five class to stay in the year 5 field and the new class to
be the current class.

I really don't have much experience in Access, I am self taught and so
I may be expecting too much. I hope I have explained myself clearly
enough and somebody can help me with this.
Thank you"

[Larry Linson]

Yes, you do need to rethink your table design. If you have a Student record
with five Fields for Class, then your table design is going to make working
with your database difficult. For a brief, overview, tutorial on data
structure, take a look at the presentation I did for my user group, entitled
"Practical Table Layout" which you can download from
http://appdevissues.tripod.com.

See if that is a help... first look at the logical design of your tables to
model the real world, NOT at how you might fill a particular type of
form/control in your user interface. It may turn out that what you had in
mind for an interface really is not the best -- if you've structured your
data based on the relationships between data items, then it will be easy to
modify; if you've structured it based on your initial impression of the
forms you might use, it is likely to be difficult to modify.

"Corinne" <CR******@aol.com> wrote in message
news:f4**************************@posting.google.c om...

I have a database that contains the details of pupils in a school.
What I would like to do may not be possible but I thought I would ask
anyway. Each year the pupils move to a different class, there are 8
classes in each year that are named with the year and the class
teachers initials e.g 5FS would be a year 5 class, teacher Fred Smith.
So every year I have to change the class the pupils are in, thereby
losing the classes they have been in previously. I would like to set
up the form so that I have current class showing on the form header.
The form has tabs that have relevant info (from sub tables/forms) for
each year, so in those places I would like to store the previous class
the pupil was in. Rather than having to type things more than once
would this senario work:

If the current class is 5* then copy to year 5 class in the year 5 tab
page
If the current class is 6* then copy to year 6 class, and so on.
So in the form header I would have a field current class and in each
tab on the form I would have year 5,6,7,8 class that would be copied
from the current class, but when year 5 class changes to year 6 I want
the year five class to stay in the year 5 field and the new class to
be the current class.

I really don't have much experience in Access, I am self taught and so
I may be expecting too much. I hope I have explained myself clearly
enough and somebody can help me with this.
Thank you"