1 Reply Latest reply on Sep 29, 2014 12:30 PM by Michael Lance

    User Filter Table Joins: One-to-Many Revisited

    Michael Lance

      Hi Friends,

       

      I have a series of tables that I need to join to a main (left) table without changing the values as they occur before any joins.

      The following are all in the attached Excel file, one table per spreadsheet.

      I've mocked up what I need in the attached, packaged workbook.

       

      The main (left) table contains student demographics and information.

      It contains the following fields:

      School ID

      Grade

      Student#

      Special Education? (1 = Yes, 0 = No)

      Free Lunch? (1 = Yes, 0 = No)

       

      There are 3 user filter tables:

      The "Central Office" table contains user information for users who need access to data by school:

      Fields include:

      School ID (left join to demographics on this)

      Login

       

      Note: since these individuals need access to all students in all schools, I would prefer to omit this table if it's possible.

       

      The "School Admin" table contains user information for users who need access to data by school and grade level

      (some need access to all grade levels in a school (i.e. the principal) and others certain levels

      (i.e. the elementary curriculum coordinator).

      Fields include:

      School ID (left join to demographics on this)

      Grade (left join to demographics on this)

      Login

       

      The "Teachers" table contains user information for users who need access to data by student (some teachers just need to see

      their own students and maybe those of others that they mentor).

      Fields include:

      School ID

      Grade

      Student# (left join to demographics on this)

      Login

       

      I've read of somewhat related cases where it was recommended to blend and use an aggregate user filter calculated field:

      Can I create a user filter based on blended data?

      I tried this but the attribute calculation did not allow me to use it as a filter (maybe I missed something?).

       

       

      ----------------------

      Bonus Question #1

      -----------------------

      Will left-joining (one-to-one) other tables to the demographics (left) table impact the solution?

       

      ----------------------

      Bonus Question #2

      -----------------------

      Can I somehow do this so that if a teacher only has access to half of a 5th grade class, they can still see the correct data or bar chart for the class as a whole yet only view underlying data for that corresponding portion to which the user filter assigns access?

       

      ----------------------

      Bonus Question #3

      -----------------------

      I typically use tables from MS Access. Is there a custom SQL to handle this at that level?

        • 1. Re: User Filter Table Joins: One-to-Many Revisited
          Michael Lance

          One work-around which seems to be working so far is to combine all users into one table (this also seems to make it easier to avoid getting duplicate counts and incorrect percentages) and assign access at the grade and school levels instead of by student. The reasoning is that student level access makes  more sense for student level views, but if someone needs access to 3 students in 5th grade (i.e. a Special Ed. teacher), they will not likely be interested in seeing a bar chart summarizing those few students.

           

          When I do this, I have to do the following to get the correct values for percentages and counts:

          Percentages: Just use number of records and compute with categorical field of interest (i.e. Special Education)

          Counts: Use Count(Distinct) of student identifier