cascading multiselect dropdown?

[benner]

I am not sure if this is the best way to organize things or if it is even
possible so any input would be helpful.

I am creating a database for teachers to plan cross-curricular units around a
pretty confusing curriculum. So here's how it goes:

The teacher selects from a list of 'themes' (can only choose one, so this is
easy.) Each 'theme' has a handful of 'descriptors'. Based on their theme
selection, they get a list box of descriptors that match that theme. I
learned how to do a little VB to get the code right to do that.

But next, they have to select from a list of 'science strands' (ie living
things, materials and matter) but in this case, they may actually choose more
that one. Then, each 'science strand' has its own 'descriptors'. Again,
they may select more than one of those for each strand.

Then, this goes on for social studies, music, drama, PE, etc... So a teacher
that is doing a unit about 'kitchen chemistry' may include elements of
science, math, language and so on and they can select all the relevant
curriculum data from these cascading listboxes. then, once all the teachers
have put in their data, the principal can see what curriculum stuff has been
taught where and when.

So as it is, I am looking to create a multiselect listbox that then updates a
second multiselect listbox. so if they choose one strand, and it has 3
descriptors, then those 3 descriptors will appear in the box. but if they
choose 2, then maybe 6 descriptors appear in the box.

this is probably not the best way to organize things. the other way that i
am thinking would be just to offer extra boxes so for each subject, there are
2 'boxes to enter 'strands' then each one of those has its own 'descriptors'
box.

this will make a pretty long and busy-looking form.

this is the first database i have ever made and it has been quite a trip
learning how to do it. i wold appreciate any guidance to get me through it
all.

thanks in advance for any contributions

 

[Jeff Boyce]

You've focused your description primarily on the way it might look/work,
i.e., the "how". But "how" depends on "what", as in what data and what data
structure you are using.

To get more specific suggestions/assessment about the "how" you've chosen,
provide more specific descriptions of the data you're starting with. For
example, if you were working on a simple enrollment system, you might have:

tblStudent
StudentID
FName
LName
DOB
... (other student-specific info)

tblClass
ClassID
ClassTitle
ClassDescription
... (other class-specific ...)

trelEnrollment
EnrollmentID
StudentID
ClassID
EnrollmentDate
... (other ...)

The relationships among these are that one student can enroll in many
classes, and one class can have many students enrolled. This means these
two tables have a many-to-many relationship, hence the need for the third
table, to show valid combinations.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[benner via AccessMonster.com]

i currently have every category in its own table. subjects are like this:

tblScienceStrands
ScienceStrand
-Living Things
-Materials And Matter
-Earth And Space
-Forces And Energy

descriptors are like this:

tblLivingThingsDescriptors
LivingThingsDescriptor
-the characteristics, systems and behaviours of humans and other
animals, and of plants
-the interactions and relationships between and amongst them [humans,
animals and plants] and with their environment

tblEarthAndSpacerDescriptors
EarthAndSpaceDescriptor
-planet earth and its position in the universe, particularly its
relationship with the sun
-the systems, distinctive features and natural phenomena that shape and
identify the planet
-the infinite and finite resources of the planet

and so on... Then I have one larger table for everything to go into.

tblUnitPlanner
UnitID
Grade Level
Unit Title
Science Strand
Science Strand Descriptor
Sceince Strand Related Concepts
Science Skills
Social Studies Strand
Social Studies Strand Descriptor
etc...

Then I created a form from the tblUnitPlanner so a teacher could click
through the curriculum and put everything in place for each unit he or she is
teaching. I used this VB code to get the cascading dropdowns:

Private Sub cboScienceStrands_AfterUpdate()
On Error Resume Next
Select Case cboScienceStrands.Value
Case "Living Things"
cboScienceStrandDescriptors.RowSource = "tblLivingThings"
cboScienceStrandRC.RowSource = "tblLivingThingsRC"
Case "Earth And Space"
cboScienceStrandDescriptors.RowSource = "tblEarthAndSpace"
cboScienceStrandRC.RowSource = "tblEarthAndSpaceRC"
Case "Materials And Matter"
cboScienceStrandDescriptors.RowSource = "tblMaterialsAndMatter"
cboScienceStrandRC.RowSource = "tblMaterialsAndMatterRC"
Case "Forces And Energy"
cboScienceStrandDescriptors.RowSource = "tblForcesAndEnergy"
cboScienceStrandRC.RowSource = "tblForcesAndEnergyRC"
End Select
End Sub

I tried to use someone else's code to have every subject and its descriptors
in one table but couldn't get it to work in the drop-down. Like I said, this
is my first database. Seems like a good way to learn. And this is the first
time I have asked for help.

A unit that a teacher plans may have one science strand, or two, or none.
Each strand may include only one of the descriptors, or two or three.

I hope it is a bit clearer what I am trying to do.

 

[Jeff Boyce]

Let me back up a couple steps ... "how" depends on "what", as in "what data,
what relationships?"

I'm not ignoring what you ... OK, to start with, I will ignore you ...<g>

Based on your earlier description, it sounds like you have:
* Curriculum Units
* Curriculum Themes
* Theme Descriptors
* Science Strands
* Strand Descriptors

and the following relationships:
* each [Curriculum Unit] has one and only one [Curriculum Theme]
* each [Curriculum Theme] (for a specific [Curriculum Unit]) can have
one-to-many (i.e., one or more) [Theme Descriptors]
* each [Curriculum Unit] has one-to-many [Science Strands]
* each [Science Strand] (for a specific Curriculum Unit] can have
one-to-many [Strand Descriptors]

If these statements are not accurate, please correct them. When working
with a relational database like Access, it's pretty essential to get the
"entities" and "relationships" nailed down before building tables, queries,
forms, reports, pretty much anything else...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[benner via AccessMonster.com]

you got it exactly right. (of course there will be much more in there,
because there will also be social studies strands w/descriptors, and drama,
and music, and visual arts etc...) and currently, i have each one of those
sets of data as an individual list on its own table, then one table that
brings it all together. i am pretty sure that i am missing a few big ideas
in database design here. some things you just need a real person to explain
and books and tutorials can't do it. so if you will bear with me, thank you.

Let me back up a couple steps ... "how" depends on "what", as in "what data,
what relationships?"

I'm not ignoring what you ... OK, to start with, I will ignore you ...<g>

Based on your earlier description, it sounds like you have:
* Curriculum Units
* Curriculum Themes
* Theme Descriptors
* Science Strands
* Strand Descriptors

and the following relationships:
* each [Curriculum Unit] has one and only one [Curriculum Theme]
* each [Curriculum Theme] (for a specific [Curriculum Unit]) can have
one-to-many (i.e., one or more) [Theme Descriptors]
* each [Curriculum Unit] has one-to-many [Science Strands]
* each [Science Strand] (for a specific Curriculum Unit] can have
one-to-many [Strand Descriptors]

If these statements are not accurate, please correct them. When working
with a relational database like Access, it's pretty essential to get the
"entities" and "relationships" nailed down before building tables, queries,
forms, reports, pretty much anything else...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

If this were mine (no particular claim of correctness...<g>), I'd create one
table (with a primary key) for each of those entitites (the first group).
Then I'd load those with their possible values (and use foreign keys to
connect "child records" to their parent(s). Then set their relationships
according to the second list. I would treat all of these as "lookup tables"
(i.e., lists of values). ("lookup tables"/good ... "lookup fields"/bad)

But that's just the start... if you don't have experience working with
"relational databases" and "well-normalized data", plan on brushing up on
these before proceeding. You can treat Access as if it were a spreadsheet
.... but you pay for that!

When you get ready to start a new curriculum, you need a place to put that
(probably [Curriculum Unit]). That table needs a unique ID for the new
curriculum, and a field to indicate which [...Theme] it belongs in (store
the value of the [...Theme] table's recordID).

And that's just the start! Once you have your uniqueID for the new
curriculum, you need a place to store the appropriate [Theme Descriptors]
for that Curriculum Unit's Theme (a new table). Since you indicated that a
Curriculum Unit's Theme can have one-to-many [Theme Descriptors], you need a
field in that table that holds the Curriculum Unit's Theme's unique ID
(i.e., who is my 'parent'?), plus a field that holds the unique ID of ONE
[Theme Descriptor]. If you have more than one [Theme Descriptor] that
relates to that Curriculum Unit's Theme, you need more than one row in this
table. And if you have more than one Theme for that particular Curriculum
unit, you need another row with the *(other)* Theme's ID plus Descriptor ID
.... for each valid combination.

Still with me? You'll need to continue this process at each level, ensuring
that each "child" record has a way to know its "parent"(s).

I'll suggest taking a run at getting the first level or two (after the
lookup tables) put together before even considering how you might use forms
and reports to display/edit/add data.

Best of luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

you got it exactly right. (of course there will be much more in there,
because there will also be social studies strands w/descriptors, and
drama,
and music, and visual arts etc...) and currently, i have each one of
those
sets of data as an individual list on its own table, then one table that
brings it all together. i am pretty sure that i am missing a few big
ideas
in database design here. some things you just need a real person to
explain
and books and tutorials can't do it. so if you will bear with me, thank
you.

Let me back up a couple steps ... "how" depends on "what", as in "what
data,
what relationships?"

I'm not ignoring what you ... OK, to start with, I will ignore you ...<g>

Based on your earlier description, it sounds like you have:
* Curriculum Units
* Curriculum Themes
* Theme Descriptors
* Science Strands
* Strand Descriptors

and the following relationships:
* each [Curriculum Unit] has one and only one [Curriculum Theme]
* each [Curriculum Theme] (for a specific [Curriculum Unit]) can have
one-to-many (i.e., one or more) [Theme Descriptors]
* each [Curriculum Unit] has one-to-many [Science Strands]
* each [Science Strand] (for a specific Curriculum Unit] can have
one-to-many [Strand Descriptors]

If these statements are not accurate, please correct them. When working
with a relational database like Access, it's pretty essential to get the
"entities" and "relationships" nailed down before building tables,
queries,
forms, reports, pretty much anything else...

Regards

Jeff Boyce
Microsoft Office/Access MVP