Educational CourseBook Database Relationships (1 Viewer)

jason73503

New member
Local time
Today, 10:31
Joined
Jul 26, 2017
Messages
8
Greetings,

I'm trying to develop a database for data-entry of Course Books used by our institutions and related materials and vocabulary for each unit within the Course Books.

I would like your insights of Database Normalization Theory to see if this approach is the most effective as I am experiencing difficulty in entering data into my main data-entry form "frmBookDetails"

The basic premise is to add a single Course Book under a "BookID" and that Book should have many related "Units".

Each "Unit" has many "Vocabulary" and "Materials" associated with them.

Within the "frmBookDetails" I would like to somehow relate the "Units" separately as in the attached img "qryBookUnits" so that I can move from one "Unit" record to the next within the main form.
 

Attachments

  • RelationshipDetails.PNG
    RelationshipDetails.PNG
    37.4 KB · Views: 184
  • qryBookUnits.PNG
    qryBookUnits.PNG
    24.7 KB · Views: 143
  • Golf02.accdb
    1.3 MB · Views: 169

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 17:31
Joined
Jul 9, 2003
Messages
16,244
I note you have yet to receive a response to your question.

I make the following observations, in part, to bring your question back to the top of the pile, and to hopefully get you to readdress the Way You Are posing your question.

Your form "frmBookDetails" has a text box to display comments however it's not linked to anything. I would expect it to be linked to a field in the underlying table "tblBooks"... Makes me think you have found a database somewhere and are hacking it around and trying to make it work. If that's the case then you're not really being fair to me or yourself...

It appears to me that you are designing the system in your head. In other words you know what you want and where you want it to be, however you don't know how to get that to work. This is a common problem, people come to MS Access expecting it to sort out this type of problem for them. It can't, it won't, it can only do what you tell it.

A better approach is to start processing the workflow, in other words do the work! As you do the work, as you collect the data into a spreadsheet or write it out on a piece of paper, then it will occur to you "what am I going to do with this information?" In other words, what outputs do I want from the process... You can't get this information by just building a database, you have to do the work, the workflow. Start at the beginning and end at the end...

Purely from an MS Access point of view I would expect to see a subform on your form "frmBookDetails" for the "many related "Units" -- you also suggest that these units also have information attached to them "Each "Unit" has many "Vocabulary" and "Materials" associated with them" this sort of hints at having subforms within subforms which in my opinion are to be avoided! But really you should get your head around a better description of what you want to do...
 

jdraw

Super Moderator
Staff member
Local time
Today, 13:31
Joined
Jan 23, 2006
Messages
15,364
Further to Uncle Gizmo's comments/advice I recommend you write a brief description of your "business" in plain English. Keep it simple and business oriented --not quasi-database forms fields etc.
You know your business and readers don't, so clarity is key.

Also, a brief description of each of your identified tables may be helpful to you and readers.
For example, I'd like to hear more about your Vocabulary --not sure what it really represents.
 

jason73503

New member
Local time
Today, 10:31
Joined
Jul 26, 2017
Messages
8
Thank you for your advice and recommendations. Let me try again here 

The user-group for this database application is for teachers and administrators at a school of foreign languages.
The basic premise is to build a database of materials that we use for different levels and courses, and each level and course uses a different book. Much like the following example:

A1 (Basic) Grammar // CourseBook: MyGrammar 1
A1 (Basic) Reading // CourseBook: MyReading 1
B2 (Intermediate) Grammar // CourseBook: MyGrammar 2
B2 (Intermediate) Reading // CourseBook: MyReading 2
…etc.
Now for each CourseBook, there are many Units (or Chapters) along with Unit Names that I want to associate Materials to use in the class with. For example,
A1 Grammar // MyGrammar 1 // Unit: 1 // Unit Name: Grammar for Beginners
A1 Grammar // MyGrammar 1 // Unit: 2 // Unit Name: More Grammar for Beginners
…etc.
As said above, one of the main purposes of this database is to associate Materials to the different Units within each CourseBook. For example:
A1 Grammar // MyGrammar 1 // Unit: 1 // Unit Name: Grammar for Beginners // Material: Activity 1
A1 Grammar // MyGrammar 1 // Unit: 1 // Unit Name: Grammar for Beginners // Material: Activity 2
A1 Grammar // MyGrammar 1 // Unit: 2 // Unit Name: More Grammar for Beginners // Material: Act 1
A1 Grammar // MyGrammar 1 // Unit: 2 // Unit Name: More Grammar for Beginners // Material: Act 2
…etc.
Lastly, as an additional bonus :). Each unit generally has anywhere between 8-10 Vocabulary words that the students must learn (memorize). So I also want to associate many Vocabulary words to each Unit as well.
I suppose my question is whether my relationships match the above criteria. I believe I have achieved my basic desire in a possible round-about way according to my queries and current structures, but I’m not sure if it’s completely optimized and/or normalized. Granted, I might have gotten ahead of myself in my determination to get it working during my learning curve, but I hope you guys can give me further my understanding of good relationships.

The database is too large to upload, but if you want to review it (please do) it's available at the following link:

h.t.t.p.s://drive.google.com/open?id=0B-juuORLTO4kU05zNUlBVkJWLUE
 

Attachments

  • RelationshipsforJuliet.PNG
    RelationshipsforJuliet.PNG
    37.1 KB · Views: 135
  • NavBarMaterials.PNG
    NavBarMaterials.PNG
    15.5 KB · Views: 143

Users who are viewing this thread

Top Bottom