Help needed with table design please please please.

[sarek007]

First time posting, used Access some years ago but no coding involved. I have been asked to create a database from an Excel worksheet and to have a form which makes it easier to input new data than putting it directly into Excel.
At the moment it's a flat sheet keeping track of students taking courses at university. I have drawn out the obvious tables, tblStudent containing LName, FName, Student ID(given by school), Active Duty(Y/N) and the VA Chapter they receive funds from and I'll use an automated Primary Key #. I thought I could also add a field for # of credit hours each student is taking and which semester it is, eg. Fall17, Spring18 etc. (But I've just thought, how will I run a query to find out what the student took in an earlier semester? The students usually attend multiple semesters)
Then there is a Location table, with the Site name and Site Facility code. Not sure how to connect the tables here though?

The major problem I'm having is how best and most efficiently to add in the courses section. Each student can take more than one course in different subjects. EG. One student may take English(course name) but then are a whole list of numbers equaling different classes, i.e. 201,203,350....... I can see how I could do a table for each course and hopefully the next field will have a drop down listing only the classes for that course. But, if the student is taking 4 courses and maybe multiple classes in the same course I cannot for the life of me see how not to have a very busy form with many many fields? To try to explain another way, Joe Bloggs is taking courses:

Course Name	Course#	Course #	Course #	Course #
English	204	206
Economics	302	306	309	401
Sociology	201	203	402A

Each course can have a ton of classes to choose from and obviously they need to be separate and not in one field so I can pull reports on how many student took say, English, Course# 204 during the Spring semester 2117.

I have honestly spent hours going through the forum and following the tutorials but I just can't get it to go click. I've created other MS Access db and understand the relational aspect. That's what makes Access so fantastic. I've just never hit this problem before.
Any and all help greatly appreciated, I've been tasked with doing this and I don't want to fail at the first hurdle!
Sorry it's so long, but I hope I've explained it well enough.
Thanks
Ann

[aytee111]

First, make a list of all the "master" information and put one subject per table, one record per entity, information that is static, such as
- students
- locations
- course names
- course numbers
- semesters

Then you need tables for information that is daily, or transactional, or annual - these tables will have records added to them for each transaction
- this student (ID) is taking this course (course number ID) in this semester (ID)

Not sure about the credit hours - is this attached to a course or a student or...?

[sarek007]

Thanks for the speedy reply. The credit hours are attached to the student.
Don't think I understand having separate tables for course names and course numbers? Each course can have multiple numbers (classes). For example, Fall 2017 semester, the Political Science course has 10 classes (numbers). If I had a table with course numbers it would be a massive dropdown list. Also some of the courses have the same course/class number. E.g. Geography has a class numbered 104, and Philosophy also has a class numbered 104.

Thanks again
Ann

[aytee111]

Google "normalization" - that is the standard by which tables are designed. Rules such as having one "subject" per table and no repetition. With your courses, you have two items, course name and course number. Using one table, the course name is repeated. This would be acceptable, just barely!

You understand that what I described for you is just a start, not your whole design. A course along with its course number will need to be linked to a semester, that would be another table.

Credit hours I assume changes all the time? Someone manually enters the information? From where? If it is a static number and never changes then it will go on the students table, otherwise not.

[ArviLaanemets]

tblStudents, StdID, StudentID, ForeName, LastName, ...;
tblCourses: CrsID, CourseName;
tblSemesters: SmsID, YearNo, SemesterNo;
tblSemesterCourses: SmsCrsID, SmsID, CrsID;
tblSemesterCourseClasses: SmsCrsClsID, SmsCrsID, Class;
tblStudentClasses: StdClsID, StdID, SmsCrsClsID, ...

To avoid long dropdowns, you have to organize entry forms properly. P.e. to attach students to course classes you design:
a) a main form where you select a student, determine year and semester, and in a continous subform you register all course classes for this student in this semester;
or
b) a main form where you select a year, semester and course class, and in a continous subform you register all students participing this course class in this semester.

Edit. Probably you need fields in tblStudent to determine the start (and end) semesters, or you have to add a table tblStudentSemesters: StdSmsID, StdID, SmsID, where you register all semesters the student is participating.

[aytee111]

Geography has a class numbered 104, and Philosophy also has a class numbered 104.

Your tables will all have a primary key which is unique. Class number is not unique so will never be a primary key.

[sarek007]

Thanks for helping. I looked at Normalization and aytee111 I see what you mean about one subject etc.
AvriLaanemets, I thought I was following you well so I created some of the tables, (after drawing them on paper). What I cannot get is how to link the courses to their classes?
I started to create a classes table (tblSemesterCourseClasses: SmsCrsClsID, SmsCrsID, Class but there are so many class numbers the list goes on forever. I.e. AVM (Aviation Management) has class300, 301, 319A, 319G, 320B, 3201, 350 and on up to 450. (There are only 25 Courses so that works.)
I do understand the form design you talked about and I'll be doing the first one, (a main form where you select a student, determine year and semester, and in a continuous sub-form you register all course classes for this student in this semester. But, I don't know how to do a sub-form.

Sorry to be slow picking this up, I've spent the whole weekend in Access training's so I am trying. I'm going to copy part of the tables I've made so far to hopefully clarify what I'm trying to do. So one student may take AVM in Fall 201760 (the 60 shows it's Fall) but they may take three different classes in the AVM course. Then on the form I have to show the students info, and the location (site) they are taking the course. This is from the Excel sheet so there are extra columns where I split the data. Didn't know how to delete them. Please let me know if this doesn't make sense. Tables are all below. Thank you so much for any help you can offer here. It's a new job and I don't want to fail at the first hurdle!

Last Name

First Name

Dawg Tag #

Site

# of UCC Course

# of Major Courses

Course

Course #

#

#

#

#

Course

#

Course

#

Course

#

Course

#

#

Active Duty

VA Chapter

Credit hours

Date of Cert

Charges

Amount Certified

Logged By

Hours Verfied

Graduated

Notes

Burton

Richard

962

Bangor

0

2

WED

460

462

30

6

9/5/2017

$ 4,125.00

$ 4,128.00

TG

Yes

Taylor

Elizabeth

239

Pensacola

3

0

PLB

115

PHYS

103

SOC

215

33

9

8/23/2017

$ 2,907.03

$ 2,904.03

GH

CrsID	CourseName
1	WED
2	AVM
3	SOC
4	Phys
5	POLS
6	ENGL

SmsID	YearNo	SemesterName	SemesterNO
5	2017	Spring	20
6	2017	Summer	40
7	2017	Fall	60

StdID	Last Name	First Name	IDNo	Active Duty	VA Ch
1	Bloggs	Joe	85021160	No	Ch 33

ID	VA Chapter
1	Ch 30
2	Ch 31
3	Ch 33

ID	Site Name	Facility Code	Site Code
1	Little Rock	114	22
3	SAC	118	WC
4	North Island	905	42
5	Palomar	802	SM

[ArviLaanemets]

Are all courses read in all semesters? When not, then you need a table tblSemesterCourses: SmsCrsID, SmsID, CrsID too.

The class will be defined for semester and course. There will be a lot of them of-course, but you never use the whole lot of them. And Access is quite comfortable with ~100000 rows in table and more - I don't think the your number of classes will be so much.

P.e.

ClsID, SmsID, CrsID, Class, and define an additional unique compound index including fields SmsID, CrsID and Class, so no class with same number can be defined for certain semester and course combination.

So, for your AVM Course in Fall 2017 you will have classes
1, 7, 2, '300'
2, 7, 2, '301'
3, 7, 2, '319A'
4, 7, 2, '319G'
5, 7, 2, '320B'
6, 7, 2, '3201'
7, 7, 2, '350'
etc.

When you design the subform, you probably will have there combos for selecting student, course, and class. For course selection combo, the query used as Row Source will be based on table tblSemedterCourses, and will have Where Caluse do display only courses available for semester estimated in Main form. And for class selection combo, the query used as Row Source will have Where Clause do display only classes available for course selected in course selection combo (And you have to write an AfterUpdate event for course selection combo to overwrite the Row Source of class selection combo, and to handle previously selected class when you change the course selection. Instead of last, you can also disable course selection when class is selected in AfterUpdate event for class selection combo) - search forum/Google for cascaded combo boxes.

[sarek007]

Thanks so much. I'm halfway through Steve Bishops video course on Access, wonderful! However, I'm still having to work on the db to produce something for my boss. Can I make sure I understand what you said to do?
1) Create a table for each course, ( AVM, GEOG, HIST etc) The fields are as you said, ClsID,(auto-number) SmsID, CrsID, Class. I've manually entered the 7, 2, 300 etc. When I've finished I'll have 25 tables, one for each course, with differing numbers of rows depending on the number of classes for that course.
I'm not sure what you mean by an additional unique compound index including fields SmsID, CrsID and Class. I have created a table for the student, and imported the list of last name, first name, dawg tag into it successfully. I also have the Course table with CrsID and Course name. Also have a table with just the VaID and VA chapter as fields, and have one for the Locations, with locID and Site name, Facility code, and Site Code as the fields. I've manually entered the data in these tables too.

I'm also not sure how to create a subform. I think I understand the concept behind cascading combo boxes. On the main input form there will be a drop down field for semester and depending which is picked, the next field will have a drop down of courses available, then the next field will have a drop down of only the classes available for that course? (Hence cascading?) How can I have the form showing the student is taking more than one course/class in the same semester? Rather than have four or five sets of fields on each form, is there a way I can add a new set only if needed? Trying to maximize use of space on the form.

In the original Excel spreadsheet there are many worksheets, one for each semester. At the moment I'm only working with Fall 2017 as I've cleaned up the data, split fields into their smallest part, (last name, first name) etc. The other worksheets need to be worked on quite a bit to get them ready to be imported.

So, here I am with all these tables but of course I'm not sure how to connect them all together?

Thanks for your help and patience, appreciate it.
Ann

[orange]

sarek007,

I recommend you download and work through this tutorial from RogersAccessLibrary.

It is an older tutorial but the concepts and procedure you will learn/experience can be used with any database.
It will take about 30-45 minutes to work through the tutorial --and you will learn.
It leads you through a procedure that you can follow. It also provides a solution.
Work through the tutorial to understand the procedure.
Then, try the same procedure using your own data.

Good luck.

[ArviLaanemets]

Not a table for every course! You'll have a single table for classes, where a class is defined for course and semester. I.e. you can have a class '300' for course 'AVM' at spring 2017 and at fall 2017, but they are separate classes. Or you can have (but of-course you don't have) a class '300' for course 'AVM' at fall 2017, and a different class '300' at same time for course 'XXX'. In both example, the primary key for table (SmsCrsClsID) will be different for every class.

To create additional indexes, open the table (tblSemesterCourseClasses) in Design view, from Design menu select Indexes - an Indexes window pops up. You see there your Primary Key defined. On next (empty) row, type the name for your new index (p.e. iuClass. Into Field Name field in same row select 1st index field (SmsCrsID). Into next empty row select 2nd field (Class). In Sort Order column, set the sort order for every field added. then activate the row with index name, and check Unique (Ignore Nulls will be set to 'No' automatically). Close Indexes window.
Now, you can't insert any combination of SmsCrsID and Class twice. And you can't register a class without filling those 2 fields too.

To create a subform, you create a form you want to act as subform at first. Then you open the form you want to put the subform into (parent form) in design view, and drag the subform from objects pane into wanted location on parent form. After that you adjust the subform (NB. There will be a new subform control in your parent form now, which has a form as source. By default both have same name, but to avoid the confusion later, it is better to rename the subform. I myself use 'fFormName' for for forms and 'sfFormName' for subforms.). You also have to check LinkChildFields and LinkParentFields properties of subform, and enter them manually when you use unbound controls instead of master table fields for linking subform to parent form.

[sarek007]

Thanks Orange, I've been through that tutorial three times now, I'm honestly not dumb but I haven't found something that's gone click yet.

[orange]

Hmm. You started with a clear description of requirements, then went through all 12 steps -- to the letter --and you don't see the result is a normalized data base design.
Did you actually do the work -each of the 12 steps?

Here's the Hernandez process in overview:

The Hernandez Process In A Nutshell:
One of the best database design books of our time is: Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design By Michael J. Hernandez.

Unfortunately, Hernandez goes into such great detail at times that the overall picture is lost. The following is a list of the major steps in the process. This list should not be use in place of reading the book. It is meant only as an adjunct to it.

1. Create a narrative that accurately and in some detail describes the business
2. Double underline each Subject and Direct Object in each sentence
3. Single underline all the rest of the nouns that describe the subject or direct object
4. Make a list of the Subjects. Try to determine which are duplicates or are not pertinent. This will be your Subject List
5. Make a list of the other nouns. Try to determine which are duplicates or are not pertinent. This will be your Preliminary Field List (PFL)
6. Group the fields in the PFL into logical groupings. Do each of these groupings have a corresponding value in the Subject list? If not, you missed a subject so you should add it.
7. Go through the revised Subject list to see if there is any data that you should be storing about that subject that you are not. If so, add it to the field list.
8. Place all the Subjects across the top of a sheet of paper and write the corresponding fields below them, check them off the list as you do.
9. Now look at your tables and fields and compare them to the Ideal Table and Ideal Field:

Elements of the Ideal Field
--------------------------------------
It represents a characteristic of the subject of the table.
It contains only a single value
It cannot be deconstructed into smaller components
It does not contain a calculated or concatenated value
It is unique within the entire database structure
It retains all of its characteristics if it appears in more than one table.

Elements of the Ideal Table
----------------------------------------
It represents a single subject
It has a primary key
It does not contain multipart fields
It does not contain mulivalued fields
It does not contain calculated fields
It does not contain unnecessary duplicate fields
It contains only the minimum redundant data

1. Create Primary keys and identify any Alternate Keys (see Hernandez)
   
   1. Note: Most tables will have Artificial Primary keys (Autonumber in MS Access).
   2. Alternate keys are used for Unique Indexes.
   
   
2. Identify relationships between the tables. Be sure you can identify it in both directions:

Each Customer can have One or More Orders
Each Order can pertain to One and Only One Customer

1. Resolve any Many-to-Many relationships with the addition of a Linking table and two One-to-Many Relationships.

Order >----------------------< Products
(Each Order can have Many Products)
(Each Product can be on Many Orders)

Order ----------< OrderProducts >---------Products

1. Create your relationships, put the Primary Key of the table on the "One" side into the table on the "Many" side.

One-To-Many
Customer Order
--------------- ----------------
CustomerID (pk)------| OrderID (pk)
CustomerName |-------< CustomerID (fk)
...other fields OrderDate
...other fields

Many-To-Many
Order OrderDetails Products
--------------- ----------------- ----------------
OrderID (pk)--------<OrderID (cpk)(fk) |---- ProductID
OrderDate ProductID (cpk)(fk)>--| ProductName
...other fields ...other fields

[sarek007]

Yes, I honestly did do it, thats how I came up with the needed tables. In fact I printed the tutorial out to make it easier to go through.
Arvil Laanemets
So this is the table I'm working on now trying to follow your instructions. It's going to be a really long list as I have to go back to Spring 2016. What I've copied for you is just so you can see the structure. I could well spend my final years on this table so want to get it right. (Also want to keep my job!!)

The picture is the basic data map I'm using except that I've since added the table Class as a many to many to both Students and Courses.
Thanks both for your patience with me. I really want to learn so have been watching tutorials til me eyes hurt. Thank you.

ClsID	Class Number	SmsID	CrsID
1	300	7	2
2	301	7	2
3	319A	7	2
4	319B	7	2
5	381	7	1
6	382	7	1
7	460	7	1
8	108	7	3
9	215	7	3

[orange]

Can you post a copy of your database?

Also, please post the narrative you wrote describing "What business this database is intended to support" -- step 1 of the Hernandez process.

What exactly are you going to use this database for? I see different course, classes, semesters, locations, amount charged, site, facility, major Course......
My gut feeling is you are trying to do too much, too quickly.
You may want a high level model --the major chunks and how they fir together. Then break a chunk into its detail and build that model--ensuring it still fits within the high level model.

Consider this analogy; You're tasked with planning, designing and developing a shopping mall. There are at least a planning phase, a design phase and a development phase. Break it into pieces. You don't do the paint color of the men's washroom on the third floor outside Starbucks at the same time you start to deal with the city regarding zoning by laws, permits required.....

There is an old story ---How do you eat an elephant? 1 bite at a time. I think it applies here. Clear description of the overall work/job. A high level model to show the major pieces and how they fit together. A list of priority tasks and deliverables. Reviews and acceptance of deliverables...... Nobody sat down with a requirement definition and built the finalized, operational database application in 1 sitting.

I posted this in response to someone having difficulty with design and relationships a couple of years ago. It may help with some context and approach. If you are working with colleagues, you may want to involve some. Sharing the load is not a bad scenario. It helps keeps focus and it brings in ideas/insight. Often we don't see the forest for the trees -- but with reviews and groups, there is a certain vetting to keep focus and priorities under control.