Hello.
I'm working at a university in Mexico this summer as an internship. Perhaps part of the trouble that I'm having with Access is that I'm using a Spanish version here at the school so I sometimes have trouble understanding Microsoft's technical explanation for why I can't do things (though I often have trouble understanding the explanations in English too).

My boss wants me to organize dozens of Excel files into a database on Microsoft Access. She wants to be able to easily create reports on individual students, spread out over several years. The reports would indicate years enrolled, grades, financial aid, etc. I've created four tables so far (with the framework for easily recreating these tables for future years)....
Virtual Student 06-07
On Campus Student 06-07
Virtual Student 07-08
On Campus Student 07-08

Each table has the following fields:
First Name
Last Name
ID Number (definitely different for each student)
Academic Major
Age
Gender
Street Address
City
State
Phone Number
School E-mail Address (definitely different for each student)
Scholarship Received
Grade Point Average
Class 1 Name
Class 1 Grade
Class 2 Name
Class 2 Grade
Class 3 Name
Class 3 Grade
Class 4 Name
Class 4 Grade
Class 5 Name
Class 5 Grade
Class 6 Name
Class 6 Grade

What do you recommend making the primary key in each table? Since the primary key needs to be something unique, I imagine school e-mail address would be an acceptable primary key. Can you have the same field be the primary key for all tables?

I want to make relationships between all tables in the future. For example, I want to link all the info from 06-07 virtual to 06-07 on campus, 07-08 virtual, 07-08 on campus (is this a bad idea?). The reason I want to do this is my boss wants to be able to retrieve the grades and list of classes one student has taken. Generally students will be only "on campus", or only "virtual", but in some cases a student might switch between the two, that's why I also want to have relationships between "virtual" and "on campus".

So, what's the best way to make relationships? Link the Student ID's to the other Student ID's in other tables? Or something else?

Should I "exhibit referential integrity"?

Should I click "actualize in cascading the relational fields"?


Thank you all very much,

Hibore

Welcome to UA -o!

Your proposed design isn't normalized... Check out this URL for several good examples:
'Kick-Start' Data Models for Database Designs

Also, peruse these links for excellent information about data normalization/database design:

SEARCHING: Database Planning/Normalization

[Database Planning/Normalization]
• 001. argeedblu's FAQ on Design Strategies
• 002. Autonumbers - What They Are / Are Not
• 003. Avoid Composite Primary Keys
• 004. Building an Access Database From the Ground Up
• 005. Creating and using Descriptive and Meaningful Field Names
• 006. Crystal's ACCESS Basics for Programming
• 007. Danny's FAQ on Reserved Words
• 008. Data Modeling Resource Center
• 009. Database Design Resource Center
• 010. Database Design ROI White Paper
• 011. Database Planning Tutorial, Part 1
• 012. Database Planning Tutorial, Part 2
• 013. Description of the database normalization basics
• 014. Fully Qualified Naming - The First Step To Normalization
• 015. GroverParkGeorge's PDF on Normalization
• 016. Jerry’s FAQ on Fully Qualified Naming
• 017. Jerry's FAQ on Glossary
• 018. Jerry's FAQ on Normalization
• 019. Naming Conventions - Hungarian Notation
• 020. New to MS Access? Suggested Readings & Things
• 021. Noah's FAQ on Normalization, Part 1
• 022. Primary Keys - Natural vs Surrogate
• 023. Principles of Entity Relationship Modeling: Get IT Right.
• 024. Programming Cost Considerations for MS Access Projects
• 025. Reddick VBA Coding Conventions
• 026. Reddick VBA Naming Conventions
• 027. Rules of Data Normalization
• 028. Software Project Development Process
• 029. Steps for Documenting a database
• 030. Steps to document VBA code using Word
• 031. Storing Calculated Values and Audit Trails
• 032. Techniques for Uniquely Identifying Database Records
• 033. Ten Reasons Software Projects Succeed
• 034. The Evils of Lookup Fields in Tables
• 035. The Ten Commandments of Access
• 036. Understanding Normalization

I really appreciate this. However, my boss requested to have the tables I did (06-07 Virtual, 06-07 On campus, 07-08 virtual, etc.) and those specific fields (she doesn't want the databases to get too complicated).

Can someone think of a way to create relationships based on the tables and fields I have?

Thank you.

I kind of understand your plight, and feel for you... but...

If that's what the Boss wants, then the Boss might as well leave everything in the spreadsheets and use Excel VBA to try to get it to work. (You probably know...) Access is a Relational Database Management System, not a flat-file database. Although it likely is possible having the table structures you propose, you will end up throwing miles of code just to get it to work. If it were me, I would get the Relational tables correctly designed, then import the data from the spreadsheets. Then the querying/coding would be MUCH easier. Maybe the boss needs some coercing to understand that the DATA is most important, not the reports. If the Data isn't done right, the output will most likely be wrong, and definitely over-complicated in attempting to get anything out of it!

Wishing you luck...

I confer and don't feel like you're being beat up on. Most if not all power-users and above will agree.

>> (she doesn't want the databases to get too complicated). <<

Then you should normalize your data ....

The UN-normalized data system will have MANY work arounds that typically involve code to ensure data concurrency and integrity (if I can use those words to describe a non-normalized system). Also, the search for data becomes, well ... rather laborious since you will be creating HUGE "Or" statements so you can search multiple fields that have the same type of data in it ... then you will have to modify EACH of those information seeking queries/SQL statements IF you add another non-normalized field (ie: Class 7 Name, Class 7 Grade). Not to mention all the Forms and Reports you will have to modify in order to accommodate a change. Also, can you tell me how you are going to find a student? ... You will have to create a UNION QUERY with all of your student tables (of course that query will have to be modified - note: a union query can only be developed in SQL view - each time you add a new Virtual or On Campus table), then search the results of that Union query. Also, how are you going to gaurentee that you do not have any duplicate student ID's in the system when you countless tables of students? ... the list of questions can go on and one and yeild one result ... a un-neccessarily EXTREMELY complicated database that is the opposite of heaven to maintain and work with.

If your boss asked you to create a system, and then dictates the table design tells me a couple of things ... which sound harsh ... so please know that I mean nothing personal as my goal with this post is to convince and educate! ...

• She is only making decisions out of responsibility and not knowledge ...
• Maybe you asked how to design it, as we all have done when we started learning ... so she had to make a decision ... but she is at the same or lower level than you with respect to db design.
• It seems you both need to examine database design priciples, and learn WHY you need to normalize.

Data normalization is the foundation of a good system and may seem "Complicated" ... but it is well worth the investment in time and effort. Even if you do not attain 3NF (Third Normal Form is the design goal of most DB developers) ... ANY amount of "Normality" proves to be benificial for the quality, growth, and maintainability of your system.

...

Ok ... I'm done for now ... whew! ... that was difficult to say ... but DB development is .. well .. sometimes complicated.

As a reminder, this post is not intended to belittle or disrespect you or your boss, only to inform/warn you that your current path of db design will yeild a non-flexible, probably short lived, non-maintainable application. If that is what you are after (Which I have actually done for some 'Quick and Dirty' info gathering). Then, be prepared to get the "Normalization" speech with each post ... unless you clearly state with justification that this is a short-lived, raw, quick-and-dirty, non-maintainable, info gathering db.

Thanks my 3c on the matter.

Forgive me: Welcome to UA -o!

We hope that you will be given the opportunity to design it correctly from the start -- if you have any road blocks, please do post what you have so far and ask questions here. There are many excellent people here who will dive in and help you -- including several Microsoft MVP and MCPs.

I second John's sentiment ... Welcome to UA! ...

.... What a welcom eh? ...

But John is correct is saying that many from here will help ... including myself! ... since I kinda let you have it from the get go! .... Please don't get discouraged ... but encouraged that we are here to help you develop a workable application.

Hi Hibore
As everyone has said you need to normalise your intended tables.

The attached gives you a rough example of what is meant by normalising.

Come back with any questions

Regards

Mike

Thank you all for your help. I'll start reading over the files and trying to normalize it.

I explained to my boss that the way the database was currently set up would not be helpful and she understood my explanation. I've begun building a database trying to make it normalized.

I downloaded the sample dB from mike60smart and found it to be quite useful. My initial confusion comes from the relationships he's set up.

So far I've created four tables:

tblStudents
StudentID (primary key, Number)
Title (Number)
First Name (text)
Father's Name
Mother's Name
Gender (text)
Date of birth (Date/year)
Street Address (text)
Neighborhood (text)
Zip Code (text)
City (text)
Municipiality (text)
State (Text)
Country (text)
Telephone (text)
work email (text)
persional email (text)
school email (text)

tblStudent Location
StudentLocationID (autonumber)
Location (text)

tblTitles
TitleID (autonumber)
title (text)

tblAcademic Program
Academic ProgramID (autonumber)
Academic Program name (text)

In each calendar year, there are seven academic periods (i.e. 200711, 200712, 200713, 200721, 200722, 200723, 200724). In each period the student can take up to 3 classes.



Here's a final list of all the information I'll want in the database (along with the courses taken and grades in those courses).
Title
Nombre
Father's Name
Mother's Name
ID Number
Academic Program
Concentration
Nacionalty
Sex
Marriage Status
On Campus or Virtual
Campus Office (4 possibilities)
Street
Neighborhood
Zip Code
City
Municipality
State
Country
Telephone
Work e-mail
Personal e-mail
School e-mail
Admisión Period
Undergraduate GPA
Admissions Exam
Admisións Exam date
Admisions Exam city taken in
Admissions Exam Score
Verbal Score
Math
Cognitive
Essay Score
English Score
Introductory Course Taken (yes or no)
Time Period of Introductory Course
Introductory course grade
On financial aid scholarship (yes/no?)
Percentage Tec pays of scholarship
Outside Institution Scholarship Program (various possibilities)
Scholarship Program Winner (various options)
Employee of Tec (yes/no)


I'll post updates as I continue along. Anyone see any relationships that I can start making?

Hi Hibore

In answer to your confusion about the relationships?

All I have sent is an example of a Student attending on Many Dates for a number of courses.

The tblStudents is the ! side of the relationship - the student attends at a particular time - tblEnrolledDates

On a particular Date the Student may enrol on a number of Courses.

So the relationships are straight 1 to Many

The list of fields you have submitted for your intended database needs to be split into tables. Each table should contain information that relates just to that table name.

tblStudents
tblAdmissions
tblAcademicPrograms

etc

Once you have split your data in the table youthen start to set up the relationships.

Cheers

Mike

Here's the tables I've created. I would appreciate if you all look at the content of the tables and the data type of the tables to see if any rearranging is in order.

Also, if anyone can clue me on what relationships would seem suitable based on these tables.

I havn't done anything about classes yet, because I don't have the list of classes these students might have taken.

Thank you.