5 Cascading Tables (1 Viewer)

I have 5 tables that i need to use in cascading field format so each narrows down the selection of the next table/field choices. The tables are set up to filter downward thus:
School to Session to Grade to CoreComponent to ActivityName and Desc

SchoolName 1, 2, 3, 4, 5, 6, 7 and 8 (all schools have all all four Sessions)
Session (Fall, Winter, Spring and Summer)
Grade K-1, 2-3, 4-5, 6-8, and 9-12
Core Component (Art, Health, Academic, SocialDevelopment)
ActivityName and Desc

Schools 1,2,3 are grade K-1, 2-3 & 4-5
School 4, 5 are grade 6-8
School 6, 7, 8 are grade 9-12

Since the SchoolName, Session, Grade and CoreComponent are static entries i was hoping there was a way to enter them so that I did not have to make the tables go like

School 1, Session:Fall - Grade K-1, Session:Winter-Grade K-1, Session:Spring-Grade K-1, Session:Summer-K-1
and outward making 4 sessions per school with four grades per each session ect and that makes repeating tables very long. :banghead:

I would really like to be able to use the Session, Grades and Core Components while only having to enter them once.

I attached graphics of the tables

Sessions dont need to repeat. They are already unique: Fall, Winter, Spring and Summer.
so there should never be more than 4, yet you keep repeating them. No session ID was needed.
BUT,
you COULD keep your existing layout and add year to each
session
1, Fall 2013
2, Winter 2013
3,Spr 2014

so every school WILL get 4 sessions. Every session/school will get a set of curriculum records.
it will get long, but it doesnt matter, access can hold millions of records.

me, i would use the 4 record sessions. there are only 4.

How would you do that, according to the coding the cascade is based on the ID number.

Private Sub cboSessionID_AfterUpdate()
' Set the ProgGrade combo box to be limited by the Session
Me.cboProgGradeID.RowSource = "SELECT tblActivityProgGrade.ProgGradeID, tblActivityProgGrade.ProgGrade FROM tblActivityProgGrade " & _
" WHERE SessionID = " & Nz(Me.cboSessionID) & _
" ORDER BY ProgGrade"
Me.cboProgGradeID = Null

EnableControls
FilterDescriptionList

End Sub

Private Sub cboProgGradeID_AfterUpdate()
' Set the CoreComp combo box to be limited by the ProgGrade
Me.cboCoreCompID.RowSource = "SELECT tblActivityCoreComp.CoreCompID, tblActivityCoreComp.CoreComp FROM tblActivityCoreComp " & _
" WHERE ProgGradeID = " & Nz(Me.cboProgGradeID) & _
" ORDER BY CoreComp"
Me.cboCoreCoPrivate Sub cboSchoolID_AfterUpdate()
' Set the Session combo box to be limited by the School
Me.cboSessionID.RowSource = "SELECT tblActivitySession.SessionID, tblActivitySession.Session FROM tblActivitySession " & _
" WHERE SchoolID = " & Nz(Me.cboSchoolID) & _
" ORDER BY Session"
Me.cboSessionID = Null

EnableControls
FilterDescriptionList

End SubmpID = Null

EnableControls
FilterDescriptionList

End Sub

Private Sub cboCoreCompID_AfterUpdate()
' Set the Activity combo box to be limited by the CoreComp
Me.cboActivityNameID.RowSource = "SELECT tblActivityName.ActivityNameID, tblActivityName.ActivityName FROM tblActivityName " & _
" WHERE CoreCompID = " & Nz(Me.cboCoreCompID) & _
" ORDER BY ActivityName"
Me.cboActivityNameID = Null

EnableControls
FilterDescriptionList

End Sub

Private Sub FilterDescriptionList()

Dim strRS As String

' Filter the list box appropriately based on the combo box selection(s)
strRS = "SELECT qryActivityDescList.Offered, qryActivityDescList.XtraPPReq, qryActivityDescList.Notes FROM qryActivityDescList"

If Not IsNull(Me.cboActivityNameID) Then
strRS = strRS & " WHERE ActivityNameID = " & Me.cboActivityNameID
ElseIf Not IsNull(Me.cboCoreCompID) Then
strRS = strRS & " WHERE CoreCompID = " & Me.cboCoreCompID
ElseIf Not IsNull(Me.cboProgGradeID) Then
strRS = strRS & " WHERE ProgGradeID = " & Me.cboProgGradeID
ElseIf Not IsNull(Me.cboSessionID) Then
strRS = strRS & " WHERE SessionID = " & Me.cboSessionID
ElseIf Not IsNull(Me.cboSchoolID) Then
strRS = strRS & " WHERE SchoolID = " & Me.cboSchoolID
End If

strRS = strRS & " ORDER BY qryActivityDescList.Offered;"

Me.lstDescriptionID.RowSource = strRS

Me.lstDescriptionID.Requery


End Sub

Private Sub EnableControls()

' Clear the combo boxes
If IsNull(Me.cboSchoolID) Then
Me.cboSessionID = Null
End If

If IsNull(Me.cboSessionID) Then
Me.cboProgGradeID = Null
End If

If IsNull(Me.cboProgGradeID) Then
Me.cboCoreCompID = Null
End If

If IsNull(Me.cboCoreCompID) Then
Me.cboActivityNameID = Null
End If

' Enable or disable combo boxes based on whether the combo box preceeding it has a value.
Me.cboSessionID.Enabled = (Not IsNull(Me.cboSchoolID))
Me.cboProgGradeID.Enabled = (Not IsNull(Me.cboSessionID))
Me.cboCoreCompID.Enabled = (Not IsNull(Me.cboProgGradeID))
Me.cboActivityNameID.Enabled = (Not IsNull(Me.cboCoreCompID))

End Sub

My 2c is that your purpose is not clear. You have built a hierarchical data structure, but possibly your purpose is not really served by that structure. What is the system for? Who's time is going to be saved using it, and doing what job(s). Is this like a course catalog?

The purpose is for an after school and summer program that offer different activities (different ones are offered each term but repeat year to year and are different per grade level) in each of the core component areas. Below i did switch the order of filter to make more sense

There are eight schools,
3 elementary; grades K-1, 2-3 and 4-5
2 middle; grades 6-8
3 high; grades 9-12

The school needs to filter down the ProgGrade (the grades combos listed above)

The programgrade needs to filter down the CoreComponet (Arts, Health, Academic and Development)

The corecomponent needs to filter down the SchoolTerm (Fall, Winter, Spring and Summer)

The schoolterm needs to filter the ActivityName
they all filter the lst desc list

tjones said:

The purpose is for an after school and summer program that offer different activities (different ones are offered each term but repeat year to year and are different per grade level)

Click to expand...

Can you provide an example of the activity data? What kind of information are we talking about here? You seem to have provided samples of all the other data, except the data the system is for.

And I don't think you want a hierarchy in which the school is the parent of all. Since you are concerned with the Activity data, that should probably be your top level parent, and if the same activity might occur at multiple schools, you will want to look into a many-to-many relationship, not the tree structure you have shown.

I would expect to see tables more like....

tActivity 'the catalog of activities - doesn't change much
ActivityID
ActivityName
Description

tSchool 'the catalog of schools - doesn't change much
ShoolID
SchoolName

tSchoolActivity 'this your most common workflow, adding records to this table...
SchoolActivityID
SchoolID
ActivityID
Session (1-4)
Year
Grade (or, maybe a link to GradeID if the grade is a complex object)

Click to expand...

See how that describes a many-to-many relationship between the activity and the school, which might serve your purpose better than the strict hierarchy you're attempting to use.

But this is where your structure has to be dictated by really looking at your data and understanding--and correctly modelling--what data belongs to what object. For instance, if the activities themselves are have embedded grade dimensions. Say you have an activity called "Grade 10 Tennis." Then maybe tActivity should look like....

tActivity 'a graded catalog of activities
ActivityID
GradeID
ActivityName
Description

Click to expand...

And then tSchoolActivity becomes...

tSchoolActivity 'grade is now defined elsewhere...
SchoolActivityID
SchoolID
ActivityID
Session (1-4)
Year
'no grade data here

Click to expand...

Anyway, maybe this offers you some ideas about alternatives to your hierarchy, and a view of the data model more centered around your purpose.

The school has to be the parent in this case, all of the programs (all 8 schools) use the same database and the coordinator for each school enters the participants from their school for each grade/component and then activity. They track each participant as they go through the grades (changing schools) so the real parent record is the participant record and in the activity the school is used to filter the activities that school offered for each grade, component, term and then the activity name.

so the coordinator would go to the participant record and open the activity page and enter:
School (1), grade (2-3), component (development), and Activity (Tiger Cubs)
School (2), grade (2-3), component (development), and Activity (Homework Help)

No two schools offer the same grade/component/activity in the term, they are individual to the school and can change every term though the ones that worked really well can be repeated though with different participants and those who took it moved up a grade.

Think of it as your school record: you went to elementary and for this grade you had such and such teacher and to these courses, then on to middle and high and finally college. if your records followed you all the way through your record is the parent and the school/teacher/courses/grades etc are the activities.

The idea is to filter down through the School/Grade/Component/Term to find the activities being offered for that school/grade/component/term as it is also necessary to keep complete records of what was offered in older terms per, and this way they don't have to scroll through the entire list of activities to find the one they need.

Sounds like you're confident in the solution you have then. If you have any questions feel free to post back,

I know whats needed but not how to code it so as not to have to repeat multiple times the tables for grade, component, and term (see single - multi jpeg above).

Is there another way to set up the table to avoid the repeats (per multi) or a way to code as so to not have to repeat. That is what I am trying to figure out. I know how to do the cascading combo boxes when each is a unique entry but here i have several tables that repeat and i really did not want to multiply using the same.

Or is there a way to combine some of the table information like grade and component? Or even more optimal would be "School, Grade, Component" into one table and then have them just fill after selection.

Thanks for all the help.

How do you set up the tables and code so that there is no repeating over and over like in my jpeg "Multi"

Its hard as three tables (ProgGrade, CoreComp and Term/Session) are not Unique entries. I am lost here :banghead:

The student is the main record and the activity comes off that (but i have removed everything but activity)

Activity is set up two ways, seperate tables off frmActivityTakes
and one table off frmActivitySchGdComp (and table tblActivitySchGdComp)

What is the purpose of your system? To me, I see a lot of static data, like a list of schools, but you don't need a database to keep track of a list of schools.

Imagine an order management system. You have a list of customers and a list of products, but those lists are mostly static; they don't change much. Then you have a list of orders, which is where we produce data on a regular basis, joining our static customers to our static products we have this dynamic order table to which we are constantly adding records.

In your system I don't see the "order" table. I don't see where you routinely add records to make your static data useful. All I see is static data. So it's hard to make recommendations, because the structure has to serve the purpose, but the purpose is not apparent. Will there be students and teachers in this system somewhere? If so, where? What role will they play? Are we trying to keep track of students participating in activities? That would be data to add every session, right? Who is doing what in the future? That might be something a database would be useful for.

And then, from a design standpoint, start building your tables around that purpose.

Yes there is more to the database but i removed it to just show the activity tables and forms. Each PARENT record is based off of the student and all records such as activities are tied together via studentID #.

this database is for an after school program and is used by eight schools from K-12 grades, the purpose is to follow a student from entry in the program thru exit or until graduation and each activity they participated in. There are new activities being added to replace ones that students did not like and activitys that were well like can repeat for a term such as:

School - Grade - CoreComp - Term - Activity
BS 9-12 Health Summer Hiking

The ActivitiesTaken table is what i think you would call your Order table as it is where all the information comes together to be stored.

School (the 1st filter to narrow down to that particular schools activities)
Grade (the 2nd filter to narrow down to just activities for that grade level)
CoreComp (the 3rd filter to narrow down to the type of core comp)
Term/Session (the 4th filter to narrow down to the correct term/session)
ActivityName (the above narrowed down to select the activity that student will participate in)

all the above to narrow the description list to display notes related to that particular activity.

I am having problems setting up the table(s) correctly because so much of the data is static and non-unique. Then the coding to tie it all together

MarkK

I think I figured out what you were saying, is this what you were think for the setup of the tables and linking? Then the cascading fields would all come off the SchoolActivity table correct?

After studying your remarks and demo of the table setup above it finally sank in with the comments regarding the order, products, customer comment.

Now to figure out the coding.

Where's the student table? Your central purpose is to track students' activities, correct? What is the point of trial modelling this problem without including the student table?

I would expect to see a StudentSchoolActivity table that joins the student to an activity at a school, which is central to your purpose. Student, School, and Activity are your (relatively) static lists, but your "order" table brings together all three. The StudentSchoolActivity record should be dated by the session and the year. The grade is, as I mentioned, possibly determined in this table too, or possibly it is a dimension of the activity, as I described in an earlier post.

You do not need a Session table since the session is, if you boil it down, a scalar time dimension of the object. You could fully describe the session in the object using a number scheme like 2016.1, 2016.2, 2016.3, and 2016.4. In this case the link itself is equal in complexity to the data it links to, making the link unnecessary. Just put the session data in the object that has a session dimension.

tStudentSchoolActivity
StudentSchoolActivityID (Primary Key - Autonumber)
StudentID (Foreign Key)
SchoolID (FK)
ActivityID (FK)
GradeID (FK - or this may be defined by the activity object)
Year
Session (a number 1 to 4)

Click to expand...

Also, avoid being over-confident that you got the table design right. The tables are your foundation, and I would build very simple interfaces on my trial tables and see if I can do the jobs that serve my purpose. You posted on this site because your data entry was too repetitive, right? And it was a table design problem. So 1) don't trust your tables, 2) build a simple UI to make sure each process works, 3) incrementally move back and forth between letting your in-work UI update your in-work tables, and vice-versa.

Wrong table design cannot be corrected using fancy UI work-arounds.

Hope this helps,

I do have the student table(s) but was trying to get the activities working right first. With the activity tables filled with test data I tried out queries to make sure it was pulling correctly, i still have to enter the coding for the fields and ensure that it filters correctly.

After a couple more tests i will then move the tables to the main database build and connect to the student information. Then test that that connection works correctly.

I am trying to do this step by step testing first in a separate builds

How do you write a student / activity database without a student table? It is central to your purpose, and should appear somewhere in the images you posted.
Happy New Year

I am extremely lost by what you are saying.

i have added in the student form and table (minimal data only but it does have the student ID number).

I can get a query Activities Taken to pull the data the way i need and filter depending on the criteria entered from the Compiled table but not the form to filter it. :banghead:

If i build the tables the way i know how for cascading fields i end up with the multi table above.

Every example i look at uses Country/State/City which is no help at all as they are all unique entries and my tables are not.

What's the difference between your ActivityTaken and ActivityCompiled tables? I don't think you need both. I don't think you need the SchoolTerm table. I don't understand your reference to Country/State/City.

Isn't the Core Competency a property of the Activity? Like "Football & Frisbee." Surely by definition that is not "Academic Achievement," so then I would expect the CoreCompID field to be in the Activity table, not the ActivityTaken table. The CoreCompetency is a dimension of the Activity. No?

Hope this helps,

I was attempting to make a table that you entered the information into (like product or maybe order a or like the combination country/state/city) where you updated the information for the semester and them pulled it into the individual records according the the activity they took. does that make sense?

it was to be the filter to compile all the information into one table than pull the information into the individual student records filtering (school, session, grade level, core component and the activity) the complete record stored on the Activity taken table attached to the student ID


The activity taken table was to record the individuals activity taken. and the information was to be pulled from the easy to update compile table.

This is only the 2nd database ive attempted and its much harder.

when using Country/State/City they are unique 1-1-1 type data but with this
i dont know how to set up the table without a multiple repeating

8schools / 4 sessions / 5 grade levels / 4 Core Comp and then finally an activity i don't understand how to set that up

tblschool
schoolid
school

tblsession
sessionid this ends up with a school listed 32 times (see multi)
schoolid
session

tblProgGrade
proggradeid
sessionid it just grows and become unweildly and hard to update
proggrade


Im not quite sure what you mean about CoreComp being a property of the Activity.

The jpg NewTable Setup has the information that is required by the program.

Does any of this make sense?