Database Template

[Kris D]

Hopeing I can get some help, actually I need a lot of help. I work at a
college for a small student service program. We only use spreadsheets to
store data. We desperately need to create a database. Our program provides
many services like counseling, subsidized child care, work-study jobs, book
loans, provide bus passes, and many other services.

We have about 8 spreadsheets for each academic year. Not sure if anyone has
a template that is simular to what I am needing that you can share with me. I
can create tables in Access with no problems, but I am not quite sure about
queries, reports, and macros....And I am not sure what tables I should and
should not create.

For example, I create a table for Students (name, student ID, Social,
address, and phone number). Students must meet with a counselor at least once
a semester to continue in the program the next semester. So I am not sure if
I create tables for each semester (I must keep track of students for each
semester)? Right now I use an excel workbook for each academic year with
three worksheets in the workbook (Fall, Spring, and Summer) and list students
in each semester as they become active.

Can someone help?

Thanks

 

[Philip Herlihy]

Hopeing I can get some help, actually I need a lot of help. I work at a
college for a small student service program. We only use spreadsheets to
store data. We desperately need to create a database. Our program provides
many services like counseling, subsidized child care, work-study jobs, book
loans, provide bus passes, and many other services.

We have about 8 spreadsheets for each academic year. Not sure if anyone has
a template that is simular to what I am needing that you can share with me. I
can create tables in Access with no problems, but I am not quite sure about
queries, reports, and macros....And I am not sure what tables I should and
should not create.

For example, I create a table for Students (name, student ID, Social,
address, and phone number). Students must meet with a counselor at least once
a semester to continue in the program the next semester. So I am not sure if
I create tables for each semester (I must keep track of students for each
semester)? Right now I use an excel workbook for each academic year with
three worksheets in the workbook (Fall, Spring, and Summer) and list students
in each semester as they become active.

Can someone help?

Thanks

This is one of those maddeningly tantalising questions where (if I only
had more time) I could spend two hours writing....

Yes, you're absolutely right that a database (and Access is a good
candidate) is a better solution. Do understand that there is a lot to
learn about Access - although it's a very rewarding process. You're
right to concentrate on the tables first: if you get the tables wrong
the overall difficulty of anything you are doing increases
exponentially, but if you have a good table design the various wizards
can make things surprisingly easy.

You should plan for some study/training. Microsoft have good training at:
http://office.microsoft.com/en-us/training/CR101582831033.aspx
... and there are excellent detailed video tutorials (monthly fee) at
lynda.com; try this free sample on table design:
http://www.lynda.com/home/Player.aspx?lpk4=31001

No, you shouldn't have a separate table for a semester. Often, when you
think of adding a new table very like another in structure, it turns out
you should have a single table and add a new _record_ for each item.
Without speculating about what data you'll need to record, I'd guess
that's what you'll need to do here.

(Speculating nevertheless I'd imagine you'd want a table of
Semesters, identified by a unique "key" (think Autonumber data type), a
"friendly name" (like "Autumn 2009") and fields for start-date,
end-date, and maybe other "dependent" things ("depends which semester")
like registration fee. You'd also have a table of Students (also with a
Key field), plus a third table "Enrollments", with its own arbitrary
Key, and "foreign" keys from the Semester and Student tables; if a
student enrolled for three semesters, there would be three records in
the Enrollments table, each containing the ID (Key) of a student and a
semester. This is the classic table design for a "many-to-many"
relationship.

Every minute you spend mastering table design will save you an hour of
head-scratching elsewhere. Get that right with this project (advice
available freely here) and you'll be amazed how quickly you can build
something to be proud of.

HTH

Phil, London