Results 1 to 9 of 9
  1. #1
    jmayojr is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Nov 2016
    Location
    Boston
    Posts
    8

    Stuck on design

    Hi,

    I'm in the process of creating a new database to hold all the student conduct records (relationship chart is below). So far it's working well, but I have one section that I am stuck at and I'm hoping that the combined brain power can help me move forward.

    The database work flow is as follows:
    1. Incident occurs and is reported
    2. Incident details are input on frmIncidentOverview with unique IncidentID's
    3. Students are then added to the incident by StudentID using frmIncidentDetail.
    4. Hearing date/time, violation(s), hearing officer are all then updated on fromIncidentOverview when students are added
    5. Meeting occurs
    6. Incidents are then updated in the database with hearing outcomes and sanctions.
    7. Cases are closed when sanctions are completed or outcome dates are satisfied (ex: probation until 12/31/17).


    Notes:
    Each incident can have more than one student involved
    Each student can have more than one incident


    Each student can have more than one sanction for each incident

    Where I'm stuck
    I'm having trouble wrapping my brain around adding multiple sanctions for each student. Sanctions also need to include due date and completed date (or completed yes/no)

    Any and all help or feedback would be appreciated.


    Click image for larger version. 

Name:	Condcut Database relationships.png 
Views:	60 
Size:	116.2 KB 
ID:	31049

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    1. Remove all sanctions from tblIncidentDetail;
    2. Add a table tblIncidentSanctions which is linked with tblIncidentDetail by AutoID (many incident sanctions for one incident detail). Into tblIncidentSanctions add a field SanctionType (main, OtherFineamt, OtherEducational) and outcomes, outcome dates, etc.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Consider this structure/relationships:
    Click image for larger version. 

Name:	Relationship1.png 
Views:	57 
Size:	183.5 KB 
ID:	31055

    One student can be involved in many incidents and one incident can have many students; need a junction table.
    One incident detail can have many sanctions and one sanction can be for many incident details; need a junction table
    One incident detail can have many violations and one violation can be for many incident details; need a junction table.



    Drat!! I just reread your post and saw the last line
    I'm having trouble wrapping my brain around adding multiple sanctions for each student. Sanctions also need to include due date and completed date (or completed yes/no)
    Maybe this??
    Click image for larger version. 

Name:	Relationship2.png 
Views:	59 
Size:	189.9 KB 
ID:	31056
    One student can have many sanctions per incident detail and one sanction can be for many student incident details; need a junction table.

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    No need for additional junction table - it only adds unnecessary complexity.

    When sanctions are linked with incident details, and OP needs a report about all sanctions for student and their statuses, the report will be based on query like
    SELECT s.StudentID, s.StudentFirstName, s.StudentLastName, io.IncidentDate, io.IncidentDesc, is.SanctionType, is.Sanction, is.SanctionValue, is.SanctionDue, is.SanctionStatus FROM (((tblIncidentDetails id LEFT JOIN tblIncidentSanctions is ON is.IncidentSanction_PK = id.IncidentSanction_FK) JOIN tblIncidentsOverview io ON io.Incident_PK = id.Incident_FK) JOIN tblStudents s ON s.Student_PK = id.Student_FK)

    (This was on fly, so I'm nut sure all brackets will be on right place - Access is tricky with multiple joins)

    When OP needs some form-subform solution to work with all sanctions for student, he can define needed relationships at form's source level, and there will be p.e. an unbound form where a student is selected and which has all sanctions from tblIncidentSanctions for this student listed in subform. All info needed from tblIncidentsOverview and tblIncidentDetails can be displayed using calculated fields - so no need use them in form relationships at all.

  5. #5
    jmayojr is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Nov 2016
    Location
    Boston
    Posts
    8
    If I did it this way, how would the data entry work for this setup? I'm sorry for the questions. This project is starting to seem like it is beyond my skill set.

    Thanks.

    Quote Originally Posted by ssanfu View Post
    Consider this structure/relationships:
    Click image for larger version. 

Name:	Relationship1.png 
Views:	57 
Size:	183.5 KB 
ID:	31055

    One student can be involved in many incidents and one incident can have many students; need a junction table.
    One incident detail can have many sanctions and one sanction can be for many incident details; need a junction table
    One incident detail can have many violations and one violation can be for many incident details; need a junction table.



    Drat!! I just reread your post and saw the last line

    Maybe this??
    Click image for larger version. 

Name:	Relationship2.png 
Views:	59 
Size:	189.9 KB 
ID:	31056
    One student can have many sanctions per incident detail and one sanction can be for many student incident details; need a junction table.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    If I did it this way, how would the data entry work for this setup?
    I would use forms/sub-forms.


    Main flow would be: create a new Incident in tblIncidentOverview, create new details in tblIncidentDetails, add Students, add sanctions.

    Would you care to post your dB with a few examples?

  7. #7
    jmayojr is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Nov 2016
    Location
    Boston
    Posts
    8
    Steve,

    Attached is my DB. With some sample students in. I'd appreciate any help I can get with the design of the forms/subforms. I'm trying to get this project done and I've been limited on the time I've had. I appreciate any help/thoughts you can provide.

    John

    Conduct Database.zip


    Quote Originally Posted by jmayojr View Post
    If I did it this way, how would the data entry work for this setup? I'm sorry for the questions. This project is starting to seem like it is beyond my skill set.

    Thanks.

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Arriving late to the party! I tried to add data to the database but couldn't. There are a lot of tables, it would really help us if you would provide us with all the tables having some data, otherwise it is hard to know what is supposed to go where.

    Here is my 2c worth:
    - a master incident table holding the incident ID, hearing date, hearing officer, main description
    - detail of incidents - the violations that occurred
    - violation/student xref (jct?) - this allows one incident/multiple violations/the ability to specify which students had which violations, in case of not all the violations applying to all the students on every incident
    - outcomes/sanctions - a table containing an outcome/sanction for each of the violation/student records - this allows each violation/student combination to be given a separate outcome/sanction
    - you don't need all those jct tables, most will be handled by queries at display time

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    John,

    Sorry - I have been slammed with lots of things and I've almost got my head above water.
    In what little spare-spare time I had, I made some changes to the dB. Take a look and see if it is getting closer.
    I changed a link in "jctStudentsIncidents" from "tblIncidentDetails" to "tblIncidentOverview".

    -------------------------------------------------------------------------------
    Question on IncidentID...... In the field description in the table, you have
    "Enter the unique incident ID - using the format SemesterYear (F17) - Incident Number. Example F17-1 for the first incident of Fall 2017."
    Lets say the last incident record is "F17-10". If I am correct, the next semester is "Winter 2017". Would the first incident then be "W17-1" or would it be "W17-11"? How does the numbering work. I ask because you should have (or at least I would have) an auto-generated IncidentID.


    -------------------------------------------------------------------------------
    I would also have a time stamp field in the incident overview table. This would allow you to see when a record was entered so that someone could not retroactively enter an incident... ie some one enters an incident for Sep17 on Dec 28 2017....
    Attached Files Attached Files

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

Similar Threads

  1. Stuck on DB design
    By lithium in forum Database Design
    Replies: 6
    Last Post: 11-04-2014, 08:38 PM
  2. Stuck on A Table/Form Design
    By dgaletar in forum Access
    Replies: 9
    Last Post: 02-26-2013, 05:41 AM
  3. stuck
    By mnghost in forum Access
    Replies: 11
    Last Post: 09-23-2012, 11:32 AM
  4. stuck
    By F17RUK in forum Queries
    Replies: 1
    Last Post: 05-22-2012, 10:13 AM
  5. Using composite keys but stuck badly in the design
    By hmushtaq in forum Database Design
    Replies: 2
    Last Post: 01-25-2011, 12:25 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