update data for 2009 - not sure of best approach

[Shelly]

Hi, this may sound a little complicated, but I'll see how I go explaining and
hopefully someone can help me out!

I have a database which I use to track attendances at our school sick bay.
Part of the benefit is that I can use the db to track which teachers are
sending more students etc etc.

As it is nearly the end of the year, I am faced with the prospect of
'rolling over' the students to next year. I need to update student class
information, without losing this years data - ie if James in in grade 2 this
year, I want to update his details to his Grade 3 class (different teacher).

I have several tables, but 'Students' is the main one concerned in my
dilemma. If I update the field 'Class', it will delete the current class -
but I need it to look at retrospective data. If I add a field such as
'2009Class', I'm worried that my forms and reports etc will no longer work.

I do realise that this is far from a perfectly designed database, however I
am hoping that someone can assist me with this.
Thanks in advance!

 

[Tom van Stiphout]

On Mon, 1 Dec 2008 20:05:01 -0800, Shelly

Curious why the original designer (you?) never realized this app might
live for more than one year. We once had a customer that went down the
road of "2009class" for his "CityBudget" app for a mid-size city, and
after a few years came to us with the app in complete disarray. The
app had become impossible to maintain, was totally dependent on this
one individual who was extremely busy with other work and only barely
remembered how he "fixed" it last year, and the deadline of the year
rollover was looming. We had to rewrite the app from scratch, making
sure that the FiscalYear was in most tables.
Often there are no shortcuts to a good db design. This is one of them.

-Tom.
Microsoft Access MVP

 

[Arvin Meyer [MVP]]

You need to add a table with the class details so that you can add many
records for the same student. Use the record with the last date value as
your current student record in the query which is the recordsource for your
absentee report.

For your initial set of records in the many-side table, make a copy of the
student table and change the autonumber to a long integer, then add a new
autonumber key. Now delete all the fields which are specific to the student
(except the ID of course) and add a date field. Use a Update query to fill
the date field with the starting date of the semester/year, and set the
default value to the starting date of the next semester/year. The once per
semester/year change the default so you do not need to type in dates for
each new record. Now link the tables on the ID field and create a query and
subform to add subsequent year data.

 

[Shelly]

Thanks Arvin, that was a lot more helpful than Tom's response. I did say that
I realise that this is a far from ideal design, but it's what I have at the
moment and I need to work with it for the time being.
I was fine until I got to where you mentioned linking the tables and
creating a query and subform. Are you able to go into more detail there?
Thanks!

 

[Arvin Meyer [MVP]]

You link the tables on the StudentID (primary/foreign keys) in Tools >>>
Relationships.

Then you create a query from the new table:

Select * From NewTable:

Then use that query as the recordsource for a continuous or datasheet form.
Now in the main form's design view, drag the new subform onto the mainform
or use the subform tool in the tool box and link the Student ID fields.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[gllincoln]

Hi Shelly,

You may need to modify your approach. - and that will require updating the
forms and reports to adjust for the changes.

You may be best served by a structure that has a table containing the
students with a unique identifier and their demographic info
(address/contact info), a table for the classes, and a transaction table
where events are recorded (student x class y this date, this or that
happened).

Grade schools often have a one to many structure where each student (the
many) is in a single class (the one)- where middle and high schools are
usually are a many to many structure where each student attends multiple
classes.

If you make the effort to have the tables and their relationships mirror the
'real world' you are tryiing to track - it usually works out better than
attempting to force the world to fit into your data structures.

To track which classes the students are enrolled in, you build a
Student-Class table that simply has the student key matched to the
appropriate class key.

For structures where students attend multiple classes you have two or more
rows for each student.

For a grade school example

Student1-Class1
Student2-Class1
Student3-Class2
Student4-Class2

For a middle school example

Student1-Class1
Student1-Class2
Student2-Class2
Student2-Class3
Student3-Class1
Student3-Calss3

This table offers a lot of flexibility - you can group on student and get
all his or her classes, or you can group on Class and get the students
enrolled in each class. You would use this structure to populate combo box
lookups so when you are entering data, you pick a student, the combo box
will contain the class(es) the student is enrolled in. This concept allows a
mix of both one (class) to many (students) and many (classes) to many
(students).

Hope this helps,
Gordon