restructuring Excel data to fit relational database

[Kurt]

I need to import an Excel spreadsheet (a school roster)
into my database and append the data to existing tables.
I will need to do this several more times in the future
when new rosters come in. Since the rosters usually have
the same field names and format, I'm hoping I can do much
of this with code.

A snapshot of the spreadsheet might look like this:

Teacher StudentFirst StudentLast Grade Room
-------------------------------------------------
Jones Mary Jane Kinder 5
Jones Kevin Brown Kinder 5
Smith Tim Allen 4 A12
Smith Wendi Misfit 4 A12
-------------------------------------------------

I realize I need to recode and rearrange the data before
I append it, because my database uses three related
tables (tblTeachers, tblStudents, and tblGrades) to hold
the information Excel contained in a flat file.

For example, once successfully imported, recoded, and
appended:

tblTeachers
------------------------------------------
TeachID TeacherLast GradeID Room
03024 Jones 0.5 5
03025 Smith 2 A12
------------------------------------------

tblStudents
----------------------------------------------------
StuID StudentFirst StudentLast TeachID GradeID
035621 Mary Jane 03024 0.5
035622 Kevin Brown 03024 0.5
035623 Tim Allen 03025 2
035624 Mary Jane 03025 2
-----------------------------------------------------

tblGrades (no change to this lookup table)
------------------------------------------
GradeID Grade
0.5 Kindergarten
1 1
2 2
----------------------

What's the best way I should proceed to restructure,
recode, and append the spreadsheet date to match my
database structure?

Do I import the whole spreadsheet > use the table
analyzer to quickly split the file into related teacher
and student tables > use a series of update queries to
recode and clean the data > then append the data into my
existing teacher and student tables?

The table analyzer will easily separate out the teacher
info. from the roster into its own table (tblTeacherNew:
autonumber teacherid (PK), teachername, grade, & room),
but how do I then append these records to my existing
tblTeachers, which already has many records, each with an
autonumber field, a different PK (5 character teachID)
than the PK of tblTeacherNew (autonumber), etc.
Immediately, a conflict will occur when I try to append
records with AutoNumber values created by the analyzer
from tblTeacherNew to records with duplicate AutoNumber
values already in tblTeachers. And then there's the
conflicting PK data types issue.

I realize this is big process. Any ideas how best to
proceed?

- Kurt (my existing table structures are listed below)

tblTeachers
-------------
TeacherAutoID (autonum)
TeachID (PK) (text) ' SchoolPrefix+NumIdNew (e.g., 03192)
NumId (text) ' unique number (just w/in school)
NumIdnew (text) ' NumId in 000 format
SchoolPrefix (text) ' e.g., 03
SchoolID (num) ' linked to tblSchools
TeacherFirst (text)
TeacherLast (text)
TeacherGradeID (num)' linked to tblGrades
RoomNumber (text)
RoomSort (text) ' Room number in 000 format

tblStudents
-------------
StudentAutoID (autonum)
StuID (PK) (text) 'SchoolPrefix+NumIdNew (e.g., 030156)
NumId (text) ' unique number (just within school)
NumIdNew (text) ' NumId in 0000 format
SchoolPrefix (text) ' e.g., 03
StudentFirst (text)
StudentLast (text)
SchoolID (num) ' linked to tblSchools
TeachID (text) ' linked to tblTeachers
GradeID (num) ' linked to tblGrades

tblGrades

 

[Joe Fallon]

What do you need help for?
You have figured it all out!

I would do this:
1. Import to a single table. (I call it a Staging table.)
Purge the table before each run so it is empty and then import the latest
file to it.

2. "Use a series of update queries to recode and clean the data".

3. "Append the data into my existing teacher and student tables."

All the queries are based on the (known) Staging table name.
Once they all exist you put all the code behind a button on a form and it
does all the steps for you in 1 click.
(Of course you need to test it a few times to ensure it is working right
before you "trust" it.)