Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2012
    Posts
    2

    Unanswered: Basic DB design for tracking monthly receipt of documents

    Hi-
    I work for a nonprofit organization and I'm trying to set up a DB that will track monthly documentation received for students in various schools. Basically, we have about 300 students attending any of 40ish private schools, and receiving different services at their schools. Each month, we receive documentation from the teachers on the services provided to the students. We don't track grades or anything, just that the service was provided and that we received the documentation. Example:

    Mary Smith goes to Springfield High and should be receiving physical therapy and speech therapy every month.

    I am a super novice at Access, so I've been reading tutorials and examples all over the place, but I am a little stumped on the best way to structure the tracking of the different services each month. Here's what I have so far as my setup:

    Student Info Table
    • Student ID (letting Access create this)
    • First Name
    • Last Name
    • DOB
    • SSN
    • (other similar biographical info)


    Services Available Table
    • Service ID (letting Access create)
    • Service Name (e.g., speech, physical therapy, occupational therapy)
    • Considering adding a description of service but it's not really necessary


    School Table
    • School ID (letting Access create)
    • School Name (e.g., speech, physical therapy, occupational therapy)
    • Address
    • Primary contact name
    • Phone #


    Here's where I get fuzzy...how to track the services that each child should be receiving (as listed on his/her educational plan) ...

    Services on Student's Plan Table
    • Student ID
    • Service ID
    • Should I have School ID on this one too?


    ...and then (this is where I am REALLY fuzzy), that we did receive documentation of each service each month.

    Documentation Received Table
    I'll want to have yes/no checkboxes in this one for each month. I know I could set up January, February, March etc. as their own fields with y/n checkboxes, but this doesn't seem like the best way to do it since we expect that this will go on year after year. Or is it best to have separate tables for each school year, and make the months their own fields?


    I appreciate any help you can give me on this! I am ok at designing queries, forms, reports, manipulating the data, but I feel like if I set up the tables wrong in the first place the whole thing will be badly designed and difficult to work with.

    Thanks so much!
    Katie

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    presumably a school provides services, and may or may not provide multiple services.

    eg school1 may provide speech and physicla thearpy, school 2 may proovide physical and occupational therapy whereas school 3 may supply all services. and so on. if thats the case then you need an entity for services and an intersection table which defines what services are available at what school. for the services table I'd suggest a priamry key that is a code/contraction of that service AND a description
    eg
    SP for speech, PT for Physical Therapy, OT... y'get the picture. whether you use 1,2,5 or more characters for the code is up to you.

    you then have a similar intersection table that associates specific pupils with specific service (needs)

    then you need to have another table identifying what school(s) that pupil is enrolled with. As you could require a historical perspective (ie pupil X went to school 1 in Winter 2012, but transferred to school 3) I wouldn't store the school enrolment at student level. especially if a pupil could be enrolled with say multiple schools either concurrently or consecutively.

    the potential problem you are going to have is how do you know what is the appropriate document set you require from the school. in the UK you'd probably have a 'statement' that identified what was required. so you'd probably need to define what specific requirements there. whether you do that as a per pupil or defien a table containing statement types and then associate those statements between pupils AND types.

    so you could have a pupil that needs physical therapy
    depending on the level of detail you need to track you coudl record that as a single intersection or have another table underneath the pupil X needs Physical Therapy with the types of physical therapy required.

    once you can provide the profile of schooling you are then in a position to register whtehr the return from the school has met that profile.

    bear in mind that you may not need to down into the detail of whats required as Im guessing there will be the legal document(s) existing somewhere. perhaps all you need to do is record on date dd/mm/yyyy the following documents were recieved, perhaps with comments suggesting waiting for the following with a follow up / action flag indicating what ever. providing you have sufficient references in the DB to track down the relevant physical document you should be OK. for bonus marks you could always suggest the system should scan incoming documents and associate the URL of each scanned document with the schools return for that pupil in that period
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    presumably a school provides services, and may or may not provide multiple services.

    eg school1 may provide speech and physicla thearpy, school 2 may proovide physical and occupational therapy whereas school 3 may supply all services. and so on. if thats the case then you need an entity for services and an intersection table which defines what services are available at what school. for the services table I'd suggest a priamry key that is a code/contraction of that service AND a description
    eg
    SP for speech, PT for Physical Therapy, OT... y'get the picture. whether you use 1,2,5 or more characters for the code is up to you.

    you then have a similar intersection table that associates specific pupils with specific service (needs)

    then you need to have another table identifying what school(s) that pupil is enrolled with. As you could require a historical perspective (ie pupil X went to school 1 in Winter 2012, but transferred to school 3) I wouldn't store the school enrolment at student level. especially if a pupil could be enrolled with say multiple schools either concurrently or consecutively.

    the potential problem you are going to have is how do you know what is the appropriate document set you require from the school. in the UK you'd probably have a 'statement' that identified what was required. so you'd probably need to define what specific requirements there. whether you do that as a per pupil or defien a table containing statement types and then associate those statements between pupils AND types.

    so you could have a pupil that needs physical therapy
    depending on the level of detail you need to track you coudl record that as a single intersection or have another table underneath the pupil X needs Physical Therapy with the types of physical therapy required.

    once you can provide the profile of schooling you are then in a position to register whtehr the return from the school has met that profile.

    bear in mind that you may not need to down into the detail of whats required as Im guessing there will be the legal document(s) existing somewhere. perhaps all you need to do is record on date dd/mm/yyyy the following documents were recieved, perhaps with comments suggesting waiting for the following with a follow up / action flag indicating what ever. providing you have sufficient references in the DB to track down the relevant physical document you should be OK. for bonus marks you could always suggest the system should scan incoming documents and associate the URL of each scanned document with the schools return for that pupil in that period
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Feb 2012
    Posts
    2
    Thank you! This is helpful. I need to work on it/digest what you said and I will probably come back with more questions.

    Luckily we don't need to store much detail--simply check off that the appropriate documentation was received each month.

    Thanks again!
    Katie

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •