Hello everyone,
May I thank you for your time in advance. Apologies for the size of this post.
I am new to access and need advice on several issues. I am trying to design a small database for about 150 students to keep track of their Maths, English and ICT levels along with personal info.
1. As yet i do not fully comprehend the benefits of Access over Excel? I have the information I require in excel at present but it is a little messy, and we soon need to start querying the information to gather info for different sources.
Now part of the switch to Access is down to a personal desire to learn how to use the software. But I am failing to understand a few things.
2. If I transfered all the information into one table in Access it differs from Excel in no manner as far as i can tell, I simply input the data for each student as I do in the excel spreadsheet. I therefore assume there is benefit to splitting the data into smaller more manageable tables?
Currently i have split it into for table as below:
Personal info
Student ID* (contains 150 records)
forname
surname
DOB
reg date
Base location (base location of each student)
Base ID*
Base location (contains 6 records)
Course name
Course ID*
Course Name (5 courses)
Course level
Level ID*
Level (5 levels)
3.I have done research on relationships and understand how they work to a degree e.g (one base can have many students but one student may have only the one base) so i would therefore create a one to many relationship between the Personal info table and the Base location table right? Which side do i use as the primary key and which is the foreign key?
4.I have tried the relationship above (3) using the Base Id primary key (base table) and created a new field called "base ID" in the "personal info" table to use as the foreign key. What i fail to see is the need for another table containing the base location info, why shouldn't I just create a field in the "personal info" table and simply type in the base name? All i do now is have to enter an ID which correlates to the base name in the "base location" table (its a hassle to remember the ID's).
I do have more questions but will leave it at that for now
Many thanks
May I thank you for your time in advance. Apologies for the size of this post.
I am new to access and need advice on several issues. I am trying to design a small database for about 150 students to keep track of their Maths, English and ICT levels along with personal info.
1. As yet i do not fully comprehend the benefits of Access over Excel? I have the information I require in excel at present but it is a little messy, and we soon need to start querying the information to gather info for different sources.
Now part of the switch to Access is down to a personal desire to learn how to use the software. But I am failing to understand a few things.
2. If I transfered all the information into one table in Access it differs from Excel in no manner as far as i can tell, I simply input the data for each student as I do in the excel spreadsheet. I therefore assume there is benefit to splitting the data into smaller more manageable tables?
Currently i have split it into for table as below:
Personal info
Student ID* (contains 150 records)
forname
surname
DOB
reg date
Base location (base location of each student)
Base ID*
Base location (contains 6 records)
Course name
Course ID*
Course Name (5 courses)
Course level
Level ID*
Level (5 levels)
3.I have done research on relationships and understand how they work to a degree e.g (one base can have many students but one student may have only the one base) so i would therefore create a one to many relationship between the Personal info table and the Base location table right? Which side do i use as the primary key and which is the foreign key?
4.I have tried the relationship above (3) using the Base Id primary key (base table) and created a new field called "base ID" in the "personal info" table to use as the foreign key. What i fail to see is the need for another table containing the base location info, why shouldn't I just create a field in the "personal info" table and simply type in the base name? All i do now is have to enter an ID which correlates to the base name in the "base location" table (its a hassle to remember the ID's).
I do have more questions but will leave it at that for now
Many thanks