Assign Courses to students so only those courses show up in forms and queries w/Student name

[June7]

No, Access should recognize name change in queries/forms/reports except for some expressions that include table name. VBA code will not recognize change. Not sure about macros.



But data should not be lost. Make a copy of db first because corruption does occasionally happen.

[MichaelA]

Steve,

In tblAssignments, you have a CourseID_FK but it doesn't have a relationship to anything. Is this intentional? SchoolYear in my fb is GradeLevel, I changed the name. I'm debating if I need School year in the sense that you are thinking. I may not need it. And if I want to view one table where I can see Student Name, Grade Level, Course, Assignment and Grade (Score). Where we I see all this together.

Also, when using all of these ID numbers, how can I see real names instead of numbers.

Thank you!

[June7]

Join tables in queries to retrieve related data.

Use multi-column comboboxes on forms.

[ssanfu]

In tblAssignments, you have a CourseID_FK but it doesn't have a relationship to anything. Is this intentional?

In table tblAssignments, how would you know which assignments belong to which course if there wasn't a CourseID_FK??

SchoolYear in my fb is GradeLevel, I changed the name. I'm debating if I need School year in the sense that you are thinking.

Not sure what fb means (Faculty of Business, Flying Bridge, Followed By ....) but if you mean changing "SchoolYear" to "GradeLevel" (Freshman, Sophomore, Junior, Senior), I can see that. (Still, might want to know what year they attended??)

And if I want to view one table where I can see Student Name, Grade Level, Course, Assignment and Grade (Score). Where we I see all this together.

You wouldn't "view one table", you would create a query. (ie a virtual table). Then use that query as the record source for a form or report. Could filter i down to one student or a group of students or a grade level.

Also, when using all of these ID numbers, how can I see real names instead of numbers.

Only the developer should be able to see the raw table data, so seeing the "Real Names" instead of the foreign key value is not important.

Seeing the "real names - it depends:
If doing data entry, then use combo boxes (as June said).
Viewing form/report, then use the query route.


After saying all that, there is nothing stopping you from using Look-up FIELDS. But, unless you really understand HOW to use look-up FIELDS, be prepared for some headaches.
Most experienced developers stay away from them..


--------------------------------------------------------------------------------------------------------------------------------------
Don't know if you've ever seen The Ten Commandments of Access...

In that page is The Evils of Lookup Fields in Tables
Note: Look-up FIELDS (BAD) are different than Look up TABLES (Good).

[MichaelA]

Thank you, again, for your help and time.

In table tblAssignments, how would you know which assignments belong to which course if there wasn't a CourseID_FK??

I get that part, I just thought the FK would need to be related in the table for it to be recognized.


Not sure what fb means (Faculty of Business, Flying Bridge, Followed By ....) but if you mean changing "SchoolYear" to "GradeLevel" (Freshman, Sophomore, Junior, Senior), I can see that. (Still, might want to know what year they attended??)

Sorry, typo, should have been Db (Database)


You wouldn't "view one table", you would create a query. (ie a virtual table). Then use that query as the record source for a form or report. Could filter i down to one student or a group of students or a grade level.

OK, that makes sense.


Only the developer should be able to see the raw table data, so seeing the "Real Names" instead of the foreign key value is not important. Got it

Seeing the "real names - it depends:
If doing data entry, then use combo boxes (as June said).
Viewing form/report, then use the query route.


After saying all that, there is nothing stopping you from using Look-up FIELDS. But, unless you really understand HOW to use look-up FIELDS, be prepared for some headaches.
Most experienced developers stay away from them..

I've removed all my look-up fields


--------------------------------------------------------------------------------------------------------------------------------------
Don't know if you've ever seen The Ten Commandments of Access...

In that page is The Evils of Lookup Fields in Tables
Note: Look-up FIELDS (BAD) are different than Look up TABLES (Good).

[MichaelA]

Thank you. I got this figured out. I created a join table for courses and students and generated a query [qryEnrollments] based on it.

Join tables in queries to retrieve related data.

Use multi-column comboboxes on forms.

[MichaelA]

I like this and I'm looking at it but when I tried to make some of the changes, I got some undesirable results in my queries and forms. I'll work on it more in the coming weeks.

"Old Programmer's Rule" is this: If you can't do it on paper then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.




A junction table is how a many-to-many relationship is implemented. An example of a many-to-many relationship would be:
One student can attend many years. (One-to-many relationship) and
One year can have many students (One-to-many relationship)

One way to look at the tables
Attachment 39812

another way to look at it
Attachment 39813

Both depict a many-to-many relationship.


You should read these. This concept is very important:

What is Normalization?
=======================
What Is Normalization, Part I: Why Normalization? http://rogersaccessblog.blogspot.com...on-part-i.html
What Is Normalization, Part II: Break it up. http://rogersaccessblog.blogspot.com...n-part-ii.html
What Is Normalization: Part III: Putting It Back Together http://rogersaccessblog.blogspot.com...-part-iii.html
What is Normalization: Part IV: More Relationships http://rogersaccessblog.blogspot.com...n-part-iv.html
What Is Normalization: Part V: Many-to-Many Relationships http://rogersaccessblog.blogspot.com...on-part-v.html




Work through these tutorials - take the time to actually do them.
Tutorials
--------------
http://www.rogersaccesslibrary.com/forum/forum46.html


------------------------------------------------------------------------------------------------------------------------------------------
I was looking at your design again. What do you think of this? (Notice the table name changes)
Attachment 39814