Results 1 to 4 of 4
  1. #1
    rb26dett26 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2016
    Posts
    8

    First Steps to Design a Database - Swim Lesson Registration Database


    I have been trying to figure out how to create a Swim Lesson Database using Microsoft Access.

    I am more curious on how others start their design process for example:

    For Swim lesson registration, you would need

    Student name
    student Phone#
    Student Age
    Parent (if any needed)
    Parent # (if student needs a guardian)
    amount paid
    amount due
    InstructorsName (table will have instructors names where this would be a drop down menu)
    registration date
    Swim Lesson dates(multiple Dates if more than one swim lesson is needed)

    This is where I get confused

    I would want to be able to input all the information above into a form and update the tables

    From what I understand, I would need

    StudentTbl--
    studentID
    studentName
    studentAge
    studentPhone#

    --ParentTbl
    parentID
    ParentName
    ParentPhone#

    --RoosterTbl
    RoosterID
    RegristrationDate
    SwimLessonDate

    InstructorTbl--

    InstructorID
    InstructorName
    InstructorPhone


    Not too worried about the instructor table being something I need to update using a form. I can input the instructors in the table manually and have the instructors name be a drop down during the form registration.

    Linking everything up where when I update the form, All of the other tables would be updated as well. I want to be able to print out reports for:

    Every class so far
    for specific weeks ,etc

    That again wouldn't be an issue with just a simple search query.


    For people who are comfortable with access or even database structures, what is your best method to design a database?

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Few things:
    1. Name your tables with "tbl" prefix so tblStudent, tblParent, etc.
    2. Add ParentID to tblStudent
    3. In tblParent, maybe add Parent1Name, Parent1Phone, Parent2Name, Parent2Phone for mom and dad, etc.
    4. in tblRoster, add StudentID and InstructorID so you can link those records to a roster record
    5. Make all the ID fields autonumber

    So your form will be the fields in tblRoster and then create combo boxes for StudentID and InstructorID to select those values to save. When you want to do a report, create query and link all the tables by the ID fields. Also create a frmStudent form to add that info and also another combo box for ParentID to select a parent. You maybe could include parent and student fields all in 1 table also.

  3. #3
    AccessPower's Avatar
    AccessPower is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    165
    I recommend the following so that you have a single junction table (tblRooster) in this case:

    tblStudent--
    studentID
    studentName
    studentAge
    studentPhone#


    tblParent--
    parentID
    ParentName
    ParentPhone#
    studentID_Junction


    tblInstructor--
    InstructorID
    InstructorName
    InstructorPhone


    tblRooster--
    RoosterID
    RegristrationDate
    SwimLessonDate
    studentID_Junction
    InstructorID_Junction


    ^The last table is where you house all lesson information. It stores elements that can link you to all of the other tables. This way you can have a form to display all of your data.

  4. #4
    AccessPower's Avatar
    AccessPower is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    165
    I recommend the following so that you have a single junction table (tblRooster) in this case:

    tblStudent--
    studentID
    studentName
    studentAge
    studentPhone#


    tblParent--
    parentID
    ParentName
    ParentPhone#
    studentID_Junction


    tblInstructor--
    InstructorID
    InstructorName
    InstructorPhone


    tblRooster--
    RoosterID
    RegristrationDate
    SwimLessonDate
    studentID_Junction
    InstructorID_Junction


    ^The last table is where you house all lesson information. It stores elements that can link you to all of the other tables. This way you can have a form to display all of your data.

    Basically the same as the suggestion above, but you can keep the parents table if you are going to have different people set as parents. If you add guardians to that list, or other authorized persons in charge of the child, you'll want another table. Otherwise go with the Bulzie's suggestion.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Swim Lesson Regristation
    By rb26dett26 in forum Access
    Replies: 4
    Last Post: 01-13-2017, 08:48 PM
  2. Replies: 2
    Last Post: 01-28-2013, 04:42 PM
  3. registration database
    By hodagh in forum Queries
    Replies: 7
    Last Post: 11-05-2012, 12:52 PM
  4. Registration Database with Family and Individual Tables
    By mcbowlby in forum Database Design
    Replies: 2
    Last Post: 08-18-2012, 05:58 AM
  5. database registration failures
    By rocheiro in forum Access
    Replies: 0
    Last Post: 02-10-2011, 09:08 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums