Can I continue the same Table for all new students?

[musicman]

Good Wed Evening All,
I am brand new here and would be pleased if I can get answers to a few questions. I offer classes to ever changing adult students.
I am using Access 2007 and rather than using a database already provided I decided to created a new database and table. I have constructed the table with all of the fields I will need to an ever changing number of students. Here is my question:


1. How can I use this same database over and over again?
2. Can I save the information in the database using a name that is unique to that group of students, ie, date of class, school where class was held, etc?
3. In other words, will I produce a different database each time I save the information?

These may seem to be questions I should know, however, I really don't. I just ordered a book, "Access 2007 for idiots". I really want to learn how to use this excellent product before I move up to the latest version. Thanks all for your help.

music

[June7]

Depends whether or not you want historical data. You could keep all data from very beginning all in one db. Or start with a new copy of blank db each year and retain archive of each year.

Personally, I would just keep it all in one db. I doubt you will ever exceed the 2GB size limit.

Yes, more data the better - date, school, etc.

[isladogs]

Good Wed Evening All,
I am brand new here and would be pleased if I can get answers to a few questions. I offer classes to ever changing adult students.
I am using Access 2007 and rather than using a database already provided I decided to created a new database and table. I have constructed the table with all of the fields I will need to an ever changing number of students. Here is my question:
1. How can I use this same database over and over again?
2. Can I save the information in the database using a name that is unique to that group of students, ie, date of class, school where class was held, etc?
3. In other words, will I produce a different database each time I save the information?

These may seem to be questions I should know, however, I really don't. I just ordered a book, "Access 2007 for idiots". I really want to learn how to use this excellent product before I move up to the latest version. Thanks all for your help.

music

Agree with June
You haven't said what type of school so the amount of detail may vary depending on your situation

tblStudents table:
Give each student a unique ID - not their name as you may get duplicates.
Make sure you have each item of information as a separate field - LastName, FirstName, Gender, DOB etc
You may wish to include a boolean (Yes/No) field called Active.
True for current students & false when they leave

Unless each student will only ever be in one school & have only one class then you should split these fields into another table tblClasses

Also consider tables for student address, contact details, medical info, attendance marks, assessment data etc

I strongly recommend you do NOT split the data into separate tables for each year or separate active students & leavers.
I made a decision to do exactly that many years ago & it caused significantly more work to maintain the database.
Instead use queries to pull the data you want from these tables

HTH

[June7]

Microsoft offers a Students database template if you want to explore. In Access2010: File > New > Education > Students

[musicman]

Good evening all and thank you so much for each response. Let me please try to explain further, my situation. We have a real estate school with real estate classes that change depending on the class offered. Instead of using the "Student Database Template" offered by Microsoft did not meet what I needed. I decided to build my own Table. Here is how I've got it structured for each student: Unique student ID, firstname, lastname, middlename, address, city, state, postalcode, phone, email (hyperlink), website (hyperlink), Fax, classname, classlocation, classdates (date/time), studentgrades and notes. Now, will I have to construct a new Template or can I use this one over and over again? Once I save this Template with student information how can it be used again? So far this Template covers every bit of information we will need for students for now. How would I save student information so that I can use this Template over and over again? I am confused about this. Thanks all.
music

[isladogs]

We have a real estate school with real estate classes that change depending on the class offered. Instead of using the "Student Database Template" offered by Microsoft did not meet what I needed. I decided to build my own Table. Here is how I've got it structured for each student: Unique student ID, firstname, lastname, middlename, address, city, state, postalcode, phone, email (hyperlink), website (hyperlink), Fax, classname, classlocation, classdates (date/time), studentgrades and notes.

As I said before, you need to split the data into several tables including:
a) students - 1 record per student
b) address & contact info - could be several records per student
c) classes e - several records per student
d) grades - as above
e) attendance - as above
etc

Then link the tables and use referential integrity to ensure these remain 'in sync'
The students table will in most cases be a 1 to many relationship to other tables

Now, will I have to construct a new Template or can I use this one over and over again? Once I save this Template with student information how can it be used again? So far this Template covers every bit of information we will need for students for now. How would I save student information so that I can use this Template over and over again? I am confused about this. Thanks all.

This doesn't make any sense.
You will keep adding records for each student in the 'school'
In most situations, the data is automatically saved as it is added.

If the database is to be used in a completely new school, the same database structure could probably be used but you would need to have empty tables (no records) for that school at the start

[ArviLaanemets]

Here is how I've got it structured for each student: Unique student ID, firstname, lastname, middlename, address, city, state, postalcode, phone, email (hyperlink), website (hyperlink), Fax, classname, classlocation, classdates (date/time), studentgrades and notes.

As you was warned several times - not a single table, but a collection of linked tables. This is a way how to do in Access. I would have something like:
tblStudents: StudentID, FirstName, LastName, MiddleName, DateOfStart, DateOfEnd;
tblStudentContacts: StudentContactID, ContactType, ContactText, IsValid;
tblContactTypes: ContactType, TypeText (TypeText values will be like phone/mobile/fax/email/web/etc.);
tblStudentAddresses: StudentAddressID, Address, City, State, ZIP, IsValid;
tblClasses: ClassID, ClassYear, ClassName, ClassLocation;
tblStudentClasses: StudentClassID, StudentID, ClassID, StudentClassStatus (StudentClassStatus values like active/finished/etc.);
tblStudentNotes: StudentNoteID, StudentID, ClassID, NoteDate, Note.

All these tables will be hidden from user. For managing data, a collection of forms is created.

I remained unsure about student grades - what will be it exactly. And as for tblStudentNotes, I assumed you did mean everyday notes - in case of session/final notes the table will differ.

[musicman]

Please let me ask another dumb question everyone and thank each of you for your help. By the way, i've been under the weather with the flu and it was a bummer. Here is my concern:
What I am trying to do is find a way to keep from having to retype a table with all of the fields each time I have a new set of students. Is there a way I can keep the Student Table with their Fields for re-use? Thanks again.

[June7]

Don't really understand question. Just continue adding students to table.

[CJ_London]

to answer your question and bearing in mind you are not listening to the advice and consequently going down the wrong road. either

1. open the table, highlight all records and hit the delete key

or 2. if you want to retain the data and create a new, empty table, in the objects window, right click on the table and select copy, then right click on the object window and select paste, a window will open so you can change the table name and gives you the option to select Structure Only, so data is not copied.

[musicman]

Good evening VIP and thank you for your help. My problem trying to explain my concern is, I don't have a good use/understanding of the Access technical terms. It's frustrating when I know what bothers me, but can't explain properly. I am trying to figure out how to develop a Template with all of the elements I need for each student group that I can use over and over again. I will try to come back with a more developed question. I've got to learn more about Access before I can ask a proper question. Thanks all for your help.

music

[CJ_London]

@musicman

As you have described your data, you are trying to apply Excel principles to Access. They are not the same thing. Excel combines data and presentation in one view. Access stores data in tables and uses forms and reports to present data.

Suggest you google 'normalisation' to get a better understanding of how data should be split into different tables.

Good luck with your project

[orange]

musicman,

Access isn't your issue as I read this thread. My first thought is that you have some preconceived view that Access is going to build you a database. Access only does what you tell it, and it only really does things efficiently if you abide by some underlying database principles.

Here is a link to info regarding database planning and design
and an attached file that outlines key database principles.

I strongly recommend you work your way through the info --watch some videos, do a tutorial or two...get a feel for what is involved.

Good luck with your project.