Updating a table

[google3luo359]

Hello,

I hope someone can tell me what would be the most efficient way to
update a table in the following scenario:

A Students table with approximately 700 records needs to be updated
each year.
A new student list comes out in the fall with say, 750 names, student
numbers etc.

How do I update the initial Student table? The new list has the new
students mixed together with the old ones so I can't just add the new
students to the end of the Students table with an append query.

An unmatched query would find the new students, but I don't know how to
throw the results from an unmatched query into the Students table.

TIA Ric

 

[Tom Ellison]

Dear Goog:

For historical purposes, I do not recommend you delete the students that do
not return.

Instead, you could have a two part natural key for the table. One part of
that key would be the SchoolYear, an integer.

Every year, you would then have a new list of students.

Add another column to the table to show whether a student returns. Default
this to Yes (true). For those who do not return, change this to No (false).
Then, run an append query that chooses all the students from the previous
year and adds them for the current year, excluding those marked as not
returning. Finally, add the new students for the year.

This is not always the best solution, but it is likely a good one in most
cases.

Tom Ellison

 

[google3luo359]

Hi Tom,

Thanks for helping out!
I understand the gist of what you were suggesting.
Unfortunately my situation falls into the 'not always the best
solution' category.

I need to keep the same main table as the students make their way into
the system and out again.
Hopefully students will last four years. In our school we have a very
high dropout-turnover rate. Those students who continue often take five
or more years to graduate instead of four.

Introducing a new table for each year would mess up the system design.
This design basically has one main form which displays a student's four
year plan/history. The form has four tabs, one for each of their four
years.
A separate table has been designed to house the data for each of the
four years.

The idea would be to cull the Students table down every few years as
the graduating students leave (backups would be kept of course) so as
to keep the Students table from growing too large and slowing down the
app.

Ric

 

[Tom Ellison]

Dear Goog:

No! Not a new table every year. One table, with a new column in it for
each school year. If a student attends the school for 4 years, he would
have 4 rows in the table, one for each year.

I suggest you re-read what I posted, but more carefully.

Tom Ellison

 

[google3luo359]

Dear Goog:

No! Not a new table every year. One table, with a new column in it for
each school year. If a student attends the school for 4 years, he would
have 4 rows in the table, one for each year.

I suggest you re-read what I posted, but more carefully.

Hi Tom,

There are many ways to interpret what you are suggesting. I've re-read
your post and still am not clear on what you are suggesting.

'a new column in it for each school year' could mean that each year a
new column will be added to the table. Or it could mean, more likely,
that in a new column, each year the school year will increase from 2006
to 2007 etc.

If I understand you correctly now, are you suggesting a main table like
this:

StudentNum Year Returning StudentLast StudentFirst StudentPassword
12345678 2005 Yes Smith Tom
123
98765431 2005 Yes Jones Jeff
123

If that's the case you're assuming that I can get a list each year
flagging students who are not returning AND also that I can get a list
of new students.

But I stated at the beginning the assumption was that I couldn't get a
list of new students, just a list of all students.
If I can get a list of new students, all I'd have to do was append them
to the Students table!

Ric

 

[Tom Ellison]

Hi Tom,

There are many ways to interpret what you are suggesting. I've re-read
your post and still am not clear on what you are suggesting.

'a new column in it for each school year' could mean that each year a
new column will be added to the table. Or it could mean, more likely,
that in a new column, each year the school year will increase from 2006
to 2007 etc.

If I understand you correctly now, are you suggesting a main table like
this:

StudentNum Year Returning StudentLast StudentFirst StudentPassword
12345678 2005 Yes Smith Tom
123
98765431 2005 Yes Jones Jeff
123

If that's the case you're assuming that I can get a list each year
flagging students who are not returning AND also that I can get a list
of new students.

But I stated at the beginning the assumption was that I couldn't get a
list of new students, just a list of all students.
If I can get a list of new students, all I'd have to do was append them
to the Students table!

Ric

 

[Tom Ellison]

Dear Goog:

I think you have the basics of my suggestion understood now.

If you get a list every year of all students, new and returning, can you
guarantee you can USE that list? Will the returning students match exactly
and accurately on the name, or some other column or set of columns. If so,
then you're in pretty good shape. Any returning student should be
identifyable by a column or set of columns that match the previous year. I
would presume that the StudentNum is the primary identifying column, but
that it may not be in the new year's list of students, which you must
somehow divide into two lists, returning students (likely repeating the
previous year's student number) and new students, to whom new student
numbers must be assigned.

This problem is totally separate from my suggestion to add a year column to
the database. That mechanism is a good one, no matter what it turns out you
must do to identify returning students in the list of the next year's
students. This design feature neither simplifies, nor complicates that
task. The difficulty of that task would depend on the consistency of data
entry, a human function, which is usually highly unpredictable.

Tom Ellison

 

[google3luo359]

Dear Goog:

I think you have the basics of my suggestion understood now.

If you get a list every year of all students, new and returning, can you
guarantee you can USE that list? Will the returning students match exactly
and accurately on the name, or some other column or set of columns. If so,
then you're in pretty good shape. Any returning student should be
identifyable by a column or set of columns that match the previous year. I
would presume that the StudentNum is the primary identifying column, but
that it may not be in the new year's list of students,

Yes, the StudentNum will always remain the same for each student,
year after year fortunately, and that is my primary key for the table.
New student numbers are already assigned before I get the new list.

I will take your advice and add a year column into the table.

There is also a good possibility of getting a list of just the new
students, which would solve my problem, but I wanted to cover all bases
and be prepared for the worst.

Thanks,

Ric

 

[Tom Ellison]

Dear Ric:

Ah, a name at last. Got tired of beeng called Goog? Well, that was part of
your signature in the return email address shown.

OK, the basic thing is simple. You can check for each incoming student in
the previous year's data when setting up a new year. If the student is
there, you could just update some information and leave the rest. If not
there, you can add the student. This could be done in two separate queries,
each filtering the "new student" data according to whether it is a returning
student from the previous year, or a new student.

Tom Ellison

 

[google3luo359]

OK, the basic thing is simple. You can check for each incoming student in
the previous year's data when setting up a new year. If the student is
there, you could just update some information and leave the rest. If not
there, you can add the student. This could be done in two separate queries,
each filtering the "new student" data according to whether it is a returning
student from the previous year, or a new student.

Tom Ellison

OK, thanks very much for your help Tom!

Ric