Looking for Ideass on Creating a DB for Building Online Courses

[Chris Hofer]

In my department at work, over the past few years, we have been using Excel
spreadsheets to keep track of our workload as it relates to building online
courses for our Technical College. The latest spreadsheet we've used has
been dubbed a "spreadsheet on steroids" because of the amount of complex
formulas and data that we have been able to gather from the spreadsheet.
But, an Access db sounds much more appealing and easier to manage...while
able to create reports off of the data.

Our spreadsheet has included the following information:

--> Instructor Name (may be duplicated)
--> Individual Course Number (there may be many section of the same course
number such as 123-456-001 and 123-456-002)
--> Course Title (may be duplicated)
--> Official Course Start Date
--> Online or Blended (part classroom/part online)
--> "New" or "Revised" (in terms of if course is new or has been re-vamped)
--> Name of person who is working on course (this is limited to the people
in my department)
--> Build hours (a sense of how many hours it takes to build an online course)
--> Feedback (just an area to type text based on feedback we receive from
instructors)
--> Comments (our deparment comments about any given course as to the status)
--> Ready to work on (date that course is "finalized" and ready to build)
--> Comments updated on (a date field that is update when we edit the record)
--> Web Specialist Status (another text field)
--> CMS Status (course managment status...another text field)
--> Date Completed (date field for when course is "ready" for instructor)

I've not had much experience using Access, and Access 2007 is new ground for
me. My first thought was to use the "Tasks" template provided by Access 2007
and modify it to meet our needs. But, maybe there is a better way? The
other part of this is that we can obtain an entire listing of all
online/blended courses that we need to prepare for a given semester, and that
data is dumped into an Excel spreadsheet (includes things like instructor,
course #, course name, start/end dates, etc.). We would need to import that
data into the Access db once it's buit.

Looking for some advice on where to start...or any ideas of what would work
best.

Chris Hofer

 

[Jeff Boyce]

Chris

I believe you already understand this concept, so ... Access is NOT Excel on
steroids. Before you begin using Access (and it has a bit of a steep
learning curve), take the time to learn about normalization and relational
databases. The time you spend now, before you design an Access database
will pay itself back many-fold in the time and headaches you'll avoid by
trying to replicate Excel data in Access.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[mscertified]

You will either need an Access developer or to learn Access yourself to
replicate (and improve) what you do in Excel in Access. Reckon on a few
months to get familiar with the basics of Access. You also cannot do much
without getting into Visual Basic so if you know that it will speed things up
vastly.

 

[Chris Hofer]

I have been reviewing how relational databases work, and I guess I'm not
seeing the full picture of how the current data we have in Excel would work
in an Access db. If we were to separate our data into tables and then make
the relationships between them (using the example fields I've given already),
what would be a logical way to begin setting up tables?

Chris Hofer

 

[Jeff Boyce]

Chris

First, step away from the computer, and pick up paper and pencil.

Unlike a spreadsheet, a relational database is the "things" you want to
store information about (physical and/or conceptual "entities"), and how
these are related to each other.

For example, if you had folks signing up as members of organizations, you
might have:

One Person can be a Member of zero, one or many Organizations.
One Organization can have zero, one, or many Members.

So you could use:

tblPerson
PersonID
LastName
FirstName
DOB
... other person-specific data

tblOrganization
OrganizationID
OrgName
... other org-specific data

trelMembership
MembershipID
PersonID
OrganizationID
MembershipDate (i.e., the date this person became a member of this
organization)
... other membership-specific data

When you have the "entities" jotted down, figure out the "relationships"
among them.

Now you're ready to start building tables (and showing the relationships).
Use one table for each entity.

Regards

Jeff Boyce
Microsoft Office/Access MVP