Simple but tricky data manipulation

E

Eric G

I have a Students table that has six fields:
LastName, FirstName, P1, P2, P3, P4

In Semester One data from this table is used by various queries and
the data is linked to other main tables.

In Semester Two, due to circumstances beyond my control, I am
presented with new Student data that has 60+ student records removed,
80+ student records added and the remaining students have their room
number data changed.

Can someone tell me the best strategy to proceed in Semester Two so
that we can still access Semester One data?

I can think of two possible approaches.
1. Somehow update Students table with the new student data and remove
the deleted students.
2. Create a Semester Two students table (Students2) and somehow be
able to link Students and Students2 to the various queries and other
tables. This way seems like it would get really messy and involve a
lot more work.

Here's what my Sem I and Sem II tables would look like:

Sem I

LAST FIRST P1 P2 P3 P4
Aasman Jesse 106D 102P 101D 103D
Abel Matthew102D 104P 106D 102C
Addis Matthew 107D
Agar Rebecca103A 101B 105B 107D
Ahmad Cassy 101B 105B 102C 108P
Aitken Ashley 106P 107A 103M 105B
Allen Stephanie101C 101M 104C 105C
Allen Stewart 109P 103C 104M 106C

Sem II (note all rooms have changed)

LAST FIRST P1 P2 P3 P4
Aasman Jesse 101D 103P 101D 104D
Abel Matthew104D 104P 106D 101C
Adman NEW** 101P 103B <----New stu

Agar Rebecca102A 104B 103B 106D
Ahmad Cassy 102B 103B 102D 108P
Aitken Ashley 105P 103A 102M 105B
Allen NEW** 102B 104P 104D <---- New stu
Allen Stephanie102D 101C 104C 105C
Allen Stewart 102P 104C 104M 106D

*note above Addis Matthew deleted as example.

Thanks for your help! Eric
 
J

John Vinson

I can think of two possible approaches.
1. Somehow update Students table with the new student data and remove
the deleted students.
2. Create a Semester Two students table (Students2) and somehow be
able to link Students and Students2 to the various queries and other
tables. This way seems like it would get really messy and involve a
lot more work.
Click to expand...

I can think of a third: properly normalizing the data. You're storing
data - periods - in fieldnames, and then trying to store multiple data
in the same record, or (even worse) creating new tables just because
related data has changed. Neither is necessary.

You have what appears to be a classic many to many relationship from
Students to Classes.

A normalized structure would have three tables:

Students
StudentID
<biographical information, nothing about classes>

Classes
ClassID
ClassName
<other info about the class as a class>

Enrollment
StudentID
ClassID
Year
Semester

If a student is enrolled in four classes in 2004 Semester 1, there'd
be four records in Enrollment; if she enrolls in three different
classes in Semester2, you'ld add three more records to Enrollment.
 
E

Eric G

I can think of a third: properly normalizing the data. You're storing
data - periods - in fieldnames, and then trying to store multiple data
in the same record, or (even worse) creating new tables just because
related data has changed. Neither is necessary.

You have what appears to be a classic many to many relationship from
Students to Classes.

A normalized structure would have three tables:

Students
StudentID
<biographical information, nothing about classes>

Classes
ClassID
ClassName
<other info about the class as a class>

Enrollment
StudentID
ClassID
Year
Semester

If a student is enrolled in four classes in 2004 Semester 1, there'd
be four records in Enrollment; if she enrolls in three different
classes in Semester2, you'ld add three more records to Enrollment.
Click to expand...

Thanks John. This makes a lot of sense. I'm going to re-work the
tables for next semester like this. I'm glad you clarified how
Enrollment table would work, with your last paragraph.

Eric
 
E

Eric G

Hi John,

I just thought of something else.
Wouldn't your solution of using the three tables depend on how the
main school database is set up?
For example if they aren't able to export
data for Class table and Enrollment table then I'd be stuck, correct?

So this all assumes that for each student I can receive exported .txt
files with:
ClassID, ClassName (for the Class table)
and StudentID, ClassID, Year, Semester (for the Enrollment table)

What if their db is only able to export the room numbers for each
student as one record, like I now have in my db?
Instead of Smith, Peter, 104D, Sem1, Per2
Smith, Peter, 103D Sem1, Per1
Smith, Peter, 106A Sem1, Per3
as you are recommending (three records).

Eric
 
J

John Vinson

Hi John,

I just thought of something else.
Wouldn't your solution of using the three tables depend on how the
main school database is set up?
For example if they aren't able to export
data for Class table and Enrollment table then I'd be stuck, correct?
Click to expand...

Incorrect. It's a bit more work but it's still perfectly possible.
So this all assumes that for each student I can receive exported .txt
files with:
ClassID, ClassName (for the Class table)
and StudentID, ClassID, Year, Semester (for the Enrollment table)

What if their db is only able to export the room numbers for each
student as one record, like I now have in my db?
Instead of Smith, Peter, 104D, Sem1, Per2
Smith, Peter, 103D Sem1, Per1
Smith, Peter, 106A Sem1, Per3
as you are recommending (three records).
Click to expand...

A "Normalizing Union Query" can be created to extract the data. Post
back with realistic fieldnames and sample data and I'll be glad to
walk you through it.
 
Top