Base - Tables @ Forms

[Greggie]

I have students who do lessons with me, I am setting up database to record the following:-

1)Student details
2)Cost/lesson
3)student lessons (topics I cover with them)

Can you help me with 3)

Student lessons have 20 topics I teach them.Teaching is individually to each student and topics vary depending on ability (so no set pattern for doing topics)
Some students may do 4 topics in one lesson while another may only do 1.
Of the 20 topics to be covered each topic is a couple of paragraphs long.
What is the best way I could link students to a topic and date without
entering all the data every time?

(I don't know, but is it possible on a form to have topics numbered 1-20 and then just select a number? but how would you link it to the data ?)
Or what is the best and easiest way to set this up.
Sorry I'am a Newbie at all this.

Thanks,

Greggie

[DACM]

Welcome Greggie,

Assuming your needs are as simple as you've described, it's probably best to create a "many-to-many" relationship between Students and Topics, where Lessons are derived through the associated "junction table."

• STUDENTS table
  ID (INTEGER AUTOVALUE) (primary key)
  FIRSTNAME (VARCHAR(50))
  LASTNAME (VARCHAR(50))
  CONTACTNAME (VARCHAR(50))
  PHONE (VARCHAR(50))
  ...
  
  TOPICS table
  ID (INTEGER AUTOVALUE) (primary key)
  TITLE (VARCHAR(100))
  DESCRIPTION (VARCHAR(10,000))
  
  LESSONS "junction table"
  ID (INTEGER AUTOVALUE) (primary key; optional 'ID' field in the classic sense, but Forms run smoother with a single constraint)
  STUDENT_ID (INTEGER) (foreign key by List Box entry using a Form)
  TOPIC_ID (INTEGER) (foreign key by List Box entry using a Form)
  DATE (DATE)

You can pull it all together in a Form based on the LESSONS table, using a couple of List Boxes and a Date field with drop-down calendar. You can optionally add a SubForm if you'd like to see the accumulated Lessons and/or Topics by Date for the selected Student in a grid on the Form. To promote your own learning and understanding of the design process, I would suggest the following links:

specifically:

• The many-to-many (n:m) relationship
  List Boxes primer
  
  and optionally:
  [Example #1] Filter/Search with Forms (leveraging SubForms)

then perhaps more comprehensively:

• Base Tutorials