DataBase Structure and Excel Imports

[Sandy]

Self taught Access and VBA would appreciate any guidance.
I have two tables tblStudents, tblClass that are causing me difficulties. I
can see the problem with my approach but am not sure how to proceed.
tblstudents has field studentID,YearID,RollClassID as well as other fields
containing information about the student. tblClass has fields RollClassID (an
autoNumber), RollClass, SchoolYear, TeacherID.

Within the data base a user can add a student through forms where the
RollClassID is a lookup based on RollClassID and RollClass in tblClass. A
user can also add new rollclasses each year through user friendly forms. Any
connections between other forms use master child links based on the
RollClassID as a roll class may have the same name from one school year to
the next . All seems good.

But whats spinning my head out is that I also want to enable users to do a
mass import of Roll classes and students from excel sheets. The Roll class xl
sheet contains RollClass & Teacher and Access is good enough to add the
autonumbers but the Student XL sheet only contains the RollClass not the
RollClassID (which really only Access Knows as it is an autonumber)

Should I be constructing a primary key using year and RollClass Name for the
tblClass? At present the StudentXL sheet makes no reference to what school
year it is so I am concerned about making sure I end up cinstructing the
correct RollClassID. I can manipulate the xl sheets via VBA if need be. Also
school years do line up with calender years as I live in Australia. I know I
have got it wrong just not sure what direction to head

 

[Jeff Boyce]

Sandy

If you know/understand/use Excel and are trying to start using Access, I'm
sorry! Many of the techniques and tricks you've learned will have to be
unlearned!

It all starts with the data for Access. For instance, I'm not quite clear
on what, in your situation, you mean by "RollClass". I believe I understand
that you can import data from Excel into Access.

However, you are NOT limited to keeping data in Access in the same structure
that it was in Excel (and it is often a very good idea NOT to). You can
still import the (raw) data from Excel, but you'll very often get better use
of Access' relationally-oriented features/functions if you then "parse" that
raw data into more permanent, well-normalized tables.

More info, please...

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Tom van Stiphout]

On Sat, 20 Mar 2010 03:10:04 -0700, Sandy

You never want to adjust your database structure because of an import.
If you don't have the RollClassID value, why not look it up using a
query? So I would attach (not import) this Excel file. It now is a
table. I would create a query qryClassCurrentYear which selects all
fields from tblClass where the Year is the current year.
Then I would create an Append query on this table and
qryClassCurrentYear. You can now pick up RollClassID from this query,
and other fields from the Excel table, and append the rows to the
Students table.

-Tom.
Microsoft Access MVP

 

[Paul Shapiro]

Self taught Access and VBA would appreciate any guidance.
I have two tables tblStudents, tblClass that are causing me difficulties.
I
can see the problem with my approach but am not sure how to proceed.
tblstudents has field studentID,YearID,RollClassID as well as other fields
containing information about the student. tblClass has fields RollClassID
(an
autoNumber), RollClass, SchoolYear, TeacherID.

Within the data base a user can add a student through forms where the
RollClassID is a lookup based on RollClassID and RollClass in tblClass. A
user can also add new rollclasses each year through user friendly forms.
Any
connections between other forms use master child links based on the
RollClassID as a roll class may have the same name from one school year to
the next . All seems good.

But whats spinning my head out is that I also want to enable users to do a
mass import of Roll classes and students from excel sheets. The Roll class
xl
sheet contains RollClass & Teacher and Access is good enough to add the
autonumbers but the Student XL sheet only contains the RollClass not the
RollClassID (which really only Access Knows as it is an autonumber)

Should I be constructing a primary key using year and RollClass Name for
the
tblClass? At present the StudentXL sheet makes no reference to what school
year it is so I am concerned about making sure I end up cinstructing the
correct RollClassID. I can manipulate the xl sheets via VBA if need be.
Also
school years do line up with calender years as I live in Australia. I know
I
have got it wrong just not sure what direction to head

Not sure I understand your table structure from the description, but the
standard approach to student-class modeling requires at least 3-4 tables:
Students (studentID, nameLast, nameFirst, dateOfBirth, etc.)- a person who
can take a class.
Courses (courseID, courseTitle, creditHours, etc.)- an instructional
offering which can be taught many times per semester, like Algebra 1 or
English 3.
ClassOfferings (courseID, year, semester, section, etc.)- a particular
offering of a course, to which students can be assigned.
Enrollments (courseID, year, semester, section, studentID, finalGrade,
etc.)- a student taking a class.

As far as importing your data, it can be difficult to match course names in
Excel to your data, since any typo causes a problem. I usually import data
like that into a temporary staging table in Access, and then use queries to
identify any bad data for correction. Once the data is clean, you can use
queries to append the data from the staging tables to your live tables, and
then delete the staged data.

 

[Sandy]

Beleive it or not Jeff I know more about access than I do excel which in the
scheme of things is probably not very much- I just didn't explain things very
clearly but I think Tom and Peter got the gist of my problem.

I was on the right track in that I was doing some checking on the
spreadsheet and then bringing it into Access as a temporary table from which
I did my updates and appends. Tom and Peter have got me thinking about
doing further checks on the temp table data using queries and particularly
the qryCurrentClass using current year to pull the RollClassID . I have had
success with that today so thanks I greatly appreciate your advise