Access beginner, database design help, please!!!! (1 Viewer)

[joju]

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

 

[jdraw]

Seems a good start and you are asking questions before jumping into Forms and subforms.

A few points:
Access and Excel are different animals. Better said - Relational database is different than spreadsheet.

Each Table has a Primary key. The Primary key is used to uniquely identify records in that table.

Normalization is a technique to remove data duplication. There are some rules to Normalization, which, if followed, will simplify your efforts with data access, storage and retrieval.

It is good practice with Access to adopt a naming convention that does not have spaces or special characters in field and object names. Names with spaces and special characters can lead to unnecessary formatting constructs that are often the source of errors.

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).

If you have a 1 to 1 relationship (base location for student), you could include it in the same table.

For more information on Database design, I am identifying some free video tutorials and a few links to other sites for references to topics relevant to design and entity relationship diagramming.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

The first few topics are well worth the read. See What is Normalization here also.

http://www.rogersaccesslibrary.com/forum/topic238.html

For help with access generally, this link has been referenced often
http://www.accessmvp.com/strive4peace/ Lots of Access Syntax, rationale, samples etc.


Good luck with your project. If you want to pursue further, just post.

 

[joju]

thanks for the links, will check them out.

 

[joju]

Ok, i watched the vids posted above and as a result have come up with the following (see attached) based around the normalization techniques discussed. I am not sure how close i am but does this look correct to those of you with superior database knowledge to I?

Do i need all the relationships i have? I need queries based around each individual base location, each student, each course and each course level. As well as a combination of all of those in one query.


As a side note, what is the best way to enter the data for the 150 students??
(tables, forms????)


Thankyou