Results 1 to 8 of 8
  1. #1
    advomystics is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    9

    Post Creating forms and relationships

    I want to create a form that will have the following information on it.
    Student First Name
    Last Name
    DOB
    Grade
    Made to Thrive (yes/no)

    Guardian First Name


    Last Name
    Phone
    Mailing Address
    City, State, Zip
    E-mail

    Select up to 6 dance classes that you would like to attend:
    Class 1
    Class 2
    Class 3
    Class 4
    Class 5
    Class 6

    I am having trouble creating relationships. Do I have all the information I need in my tables?

    I would love to attach my file but for some reason I cannot.

  2. #2
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    1, all tables are missing a primary key (PK)
    2. student table needs a family key (FK) to link to the primary key in the guardian table
    3. your current classes table is wrong and should be deleted. Instead
    4. you need a classes table (classPK and class name)
    5. you need a 'classes attended' table (ClassattendedPK, ClassFK and StudentFK)

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    In addition to Ajax's comments, I would add

    Do not have embedded spaces in field or object names. Use only alphabetics and "_" underscore.
    You may get some other ideas from this free data model from Barry Williams.

  4. #4
    advomystics is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    9
    These are not my tables: The tables I have are as follows.

    1.jcnClassLists
    -ClassID
    -StudentID

    2.jcnStudentToGuardian
    -StudentID
    -GuardianID

    3. tblCharges
    -ChargeID
    -ChargeName
    -ChargeAmount

    4. tblCostumes
    -CostumeID
    -CostumeName
    -CostumeClass
    -CostumeCost

    5. tblDanceClasses
    -ClassID
    -ClassName
    -ClassDay
    -ClassStartTime
    -Teacher
    -Cost

    6. tblGuardians
    -GuardianID
    -FirstName
    -LastName
    -PhoneNumber
    -EmailAddress

    7. tblPayments
    -PaymentID
    -PaymentSource
    -PaymentAmount
    -PaymentDate

    8. tblStudents
    -StudentID
    -FirstName
    -LastName
    -DOB
    -Grade
    -MadeToThrive

    9. tblTeachers
    -TeacherID
    -TeacherName

    I want to create a form with the information from my first post but I am not 100% sure on my relationships. I have everything written out on paper and now I am trying to apply it to Access. For some reason I cannot upload a picture of my relationships to this site. Any suggestions on how to upload? Any suggestions on my relationships to create my form? I can e-mail my database to someone if that helps. thanks.

  5. #5
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Can a student have more that one guardian? If no, then you do not need 2.jcnStudentToGuardian and instead have GuardianID in tblStudents. But I'll assume they can.

    However otherwise, looks OK - a class has one teacher, linked Teacher>TeacherID, Students can have more than one guardian so your junction table is right

    With regards the form, general rule is one table, one form, the relationships are 'represented' by using subforms

    1. main form - tblStudents
    2. guardian subform 1 - jcnStudentToGuardian (continuous) - hide the studentID and make GuardianID a combo with a rowsource of tblGuardians
    3. classes subform - jcnClassLists (datasheet or continuous) - hide the studentID and make classID a combo with a rowsource of tblDanceclasses

  6. #6
    advomystics is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    9
    Thank you...I did not think about subforms.

  7. #7
    advomystics is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    9
    Do you have a recommended site to learn how to create sub-forms? Your explanation is a little over my head.

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

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

Similar Threads

  1. Replies: 2
    Last Post: 03-05-2015, 04:26 PM
  2. Creating Relationships between tables
    By jesterling in forum Access
    Replies: 2
    Last Post: 10-30-2012, 11:06 PM
  3. Replies: 7
    Last Post: 10-04-2012, 01:21 PM
  4. Replies: 3
    Last Post: 09-17-2012, 07:53 AM
  5. Creating Relationships and Queries
    By nacho in forum Database Design
    Replies: 4
    Last Post: 04-16-2010, 03:22 AM

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