I don't have much hair left...

[DreamFreak]

I'm new to the forum and have read a lot of posts but can't seem to wrap my head around what I'm even in need of...

I'm building a database (fairly new to Access) for a mental health facility which (I believe) is a many-to-many.

I've been working feverishly for weeks and can't figure out how to fix what I'm doing or if I've even done it correctly.

Table include:
Clients
Long-Term Goals (LTG)
Long-Term Goal Objectives (aka Short-Term Goals - STG)
Methods (i.e. consultations, interventions, suggestions, etc.)
Strategies (i.e. provide instruction, reinforcement, awareness, etc.)
Materials (i.e. books, toys, games, therapy balls, etc.)
Who Provides (i.e. parent, teacher, student, Occupational Therapist, etc.)
Evaluations (i.e. child report, fine motor eval, teacher observation, etc.)
Qualifiers (i.e. sizes, accuracy, duration, quality, strength, body movements, etc.)
Key (i.e. adequate progress, limited progress, regression, etc.)
Treatment Units (i.e. 15 to 120 minutes in 15 minute increments)
Billing Modifiers (how the services are delivered...outpatient, supervised, etc.)
Billing Codes (State codes and specific descriptions)

Each client could potentially have anywhere from 8 to 11 LTG's. Each LTG could potentially have multiple STG's.

I need forms for the initial Plan of Care, 6-mon. Progress Report, Daily Notes and possibly a couple others. Each of the 3 listed will have the Client's name, DOB, period covered, physician, diagnosis, frequency/duration of services, setting (office, home, school, etc.), anticipated length of service and prognosis. Each will also have to list what the LTG's are and each of the STG's that fall under each individual LTG.

I'm so lost right now...I have all the tables typed in (except the rest of the STG's) and can't seem to figure out how to come up with junction tables to make all of these table relational.

PLEASE HELP! I'm gonna be bald by the end of the week if I can't figure this out. Thanks so much in advance!

DreamFreak

 

[Beetle]

First, you need to keep in mind that we don't know anything about the real world intricacies of what goes on at your facility. A few things can be gleaned from what you posted but there are more unanswered questions than answered.

My first question would be, when you break things down to the lowest common denominator, so to speak, what is it that is at the core of what you do (and by extension, what it is that you are trying to model in your database)?

Is it therapy sessions?

If so, what is the nature of the sessions? Individual therapy? Group? Some combination of both?

You said that the goals are related to the Client (although, if therapy is what you do, could it be that the goals might be more appropriately related to the therapy sessions?), but what about some of these other entities (Methods, Strategies, Materials, etc.)? What (and how) are they related to?

These are the types of questions that you need to answer (for yourself, as well as for us if we are to help you) before a proper table structure can be determined.

 

[DreamFreak]

First, you need to keep in mind that we don't know anything about the real world intricacies of what goes on at your facility. A few things can be gleaned from what you posted but there are more unanswered questions than answered.

My first question would be, when you break things down to the lowest common denominator, so to speak, what is it that is at the core of what you do (and by extension, what it is that you are trying to model in your database)?

Therapy sessions with children. For example...Mary Smith (child) goes in for her first meeting...from that meeting a Plan of Care is written. The person doing the eval fills out a form and chooses between 1 and 8 LTG's for that child. (There are only 8 to choose from and each one is used ONLY once in this eval).

So let's say M. Smith's first LTG is Gross Motor (GM). Under GM there would be any number of STG's to choose from and likely more than one would be chosen. (Example...GM-1 Will jump forward on both feet - GM-2 Will catch 10" ball 2 out of 3 times {the ball in this example would be considered one of the materials used})

M. Smith's second LTG is Sensory Processing (SP). Under SP you might find the following:
SP-1 Will maintain eye contact with adult during social exchange 90% of time
SP-2 Will improve visual attending skills during hand-eye activities
SP-3Will tolerate grocery store outings 20 minutes 3 out of 4 occasions

In the example above, SP-1 requires no specific materials. SP-2 could use any number of different materials which would be listed on the form and/or reports. SP-3 would require a parent or caregiver be present and document, therefore the "Who Provides" table is required.

Typically it would be one-on-one therapy sessions, however, group sessions are also used sometimes.

Does this help?!?

 

[Beetle]

Does this help?!?

Yes, it's a start, but, as is often the case, answers lead to more questions.

Therapy sessions with children. For example...Mary Smith (child) goes in for her first meeting...from that meeting a Plan of Care is written. The person doing the eval fills out a form and chooses between 1 and 8 LTG's for that child. (There are only 8 to choose from and each one is used ONLY once in this eval).

So is this first meeting considered a therapy session as well, or is it more of an initial consultation? If the latter, then it may need a separate table if there are attributes of the initial consult that differ from the attributes of a therapy session.

So let's say M. Smith's first LTG is Gross Motor (GM). Under GM there would be any number of STG's to choose from and likely more than one would be chosen. (Example...GM-1 Will jump forward on both feet - GM-2 Will catch 10" ball 2 out of 3 times {the ball in this example would be considered one of the materials used})

Does each session focus on only one LTG, or is it possible to work on more than one LTG in a single session?

M. Smith's second LTG is Sensory Processing (SP). Under SP you might find the following:
SP-1 Will maintain eye contact with adult during social exchange 90% of time
SP-2 Will improve visual attending skills during hand-eye activities
SP-3Will tolerate grocery store outings 20 minutes 3 out of 4 occasions

What happens when a client successfully meets one of these STG's? Is it simply a Yes/No answer given by the therapist/provider, or is detailed answer/information required?

Typically it would be one-on-one therapy sessions, however, group sessions are also used sometimes.

Does a group session have the same set of attributes as a 1-on-1 session (in other words, are you keeping track of the exact same things, just in a group session)?

BTW - You mentioned you're feeling frustrated, but this isn't exactly an easy database to model. For someone without much experience, you more or less jumped right into the deep end of the pool.

 

[DreamFreak]

I can't figure out how to quote like you did...so bear with me...lol.

I do have more than just basic experience with Access...just haven't used it in a long time. I am far more comfortable with Excel but it just won't work in Excel the way it needs to.

So is this first meeting considered a therapy session as well, or is it more of an initial consultation? If the latter, then it may need a separate table if there are attributes of the initial consult that differ from the attributes of a therapy session.

First meeting is the initial consultation...it's where the plan is made on what the child needs. The current form looks a lot like this:
Client Name:
Date of Birth:
Period Covered: (This will be entered manually as it's just dates)
Physician:
Diagnosis:
Frequency/Duration of Sessions:
Session Setting:
Anticipated Length of Service:
Prognosis:
Progress Report Due:
(The Progress Report looks identical to this but with a notes section for the OT to fill in if needed. It also lists the Keys {Adequate Progress, Limited Progress, Regression, Etc.} for each STG)
Long Term Goals: (1 or more of the 8 below are listed)
SP - Sensory Processing
GM - Gross Motor
FM - Fine Motor
VP - Visual Perceptual
VM - Visual Motor
KR - Kindergarten Readiness
SC - Self-Care
OM - Oral Motor

Short Term Goals:
Goal # Objective
Right now every single STG that is likely to be used is listed (20 plus pages are printed out every single time a new evaluation is done because nothing is in the computer correctly)

Each Long-Term Goal has at least one STG listed under it's heading (but the potential to add more is necessary depending on each individual child's situation...so we need to be able to add a new STG when needed)

SP has 27 different STG's
GM has 21
FM has 12
VP has 5
VM has 6
KR has 11
SC has 15
OM has 8

Does each session focus on only one LTG, or is it possible to work on more than one LTG in a single session?

Each session focuses on ALL the LTG's that are listed in the initial Plan of Care (POC). Sessions are done in "units" which are broken down into 15 minute increments. So, if they work on LTG #1 for 2 units, that's 30 minutes. They might work on each for just 15 minutes or they may have to combine multiple LTG's into each 15 minutes unit because the state only funds a certain amount of time for each child per session. But, regardless...all of the goals are worked on in every subsequent session somehow.

What happens when a client successfully meets one of these STG's? Is it simply a Yes/No answer given by the therapist/provider, or is detailed answer/information required?
When a goal (LT or ST) is successfully met (aka mastered), the key marks that section with an M. (M= Mastered) Each therapist MUST by state law document each session with Log Notes...this is where they would put additional info when necessary.

Does a group session have the same set of attributes as a 1-on-1 session (in other words, are you keeping track of the exact same things, just in a group session)?
Group sessions are tracked the same exact way...

BTW - You mentioned you're feeling frustrated, but this isn't exactly an easy database to model. For someone without much experience, you more or less jumped right into the deep end of the pool.
And I'm swimming like a mad-woman trying to get my feet back on solid ground. I am normally pretty good at figuring things like this out but I'm struggling with this one. I have all the info I've mentioned in individual tables right now but can't get them related.

What I basically need is a form that someone with little to no computer experience can use to enter client info...what their LT and ST goals are (by choosing from a list), etc.

I then need to be able to print out an initial POC and then the subsequent forms/reports on each child's progress without having to print out a 21 plus page document where more than 1/2 the items aren't even used.

Am I getting more and more confusing...or more clearer...I can't even tell at this point. I am smiling though...so that must mean something good... (-:

 

[DreamFreak]

After sleeping on it...I'm wondering if some of these "tables" I've already created should be changed to drop-down menus instead. The time (in units of 15) only goes from 15 to 120. The materials never change and aren't always used...the strategies, who provides, etc.

Something to ponder...

 

[Beetle]

I can't figure out how to quote like you did...so bear with me...lol.

Copy some text from an existing post. In your reply click the quote tag button, which will insert quote tags into your post. Paste the text between these quote tags.

After sleeping on it...I'm wondering if some of these "tables" I've already created should be changed to drop-down menus instead. The time (in units of 15) only goes from 15 to 120. The materials never change and aren't always used...the strategies, who provides, etc.

That is what some of your tables will be ("lookup" tables that serve as the Row Sources for combo boxes - aka "drop down menus"). You can use Value Lists directly in the combo boxes, instead of tables, but I don't recommend it because if you need to add more values to the list at some point you've get to go back and update the Value List for every combo/list box that uses that "list". As far as the time units go, I'm not even sure you would need a table for that. If they are always 15 minutes then all you should need to store in the number of "units", then simply multiply by 15 to derive the time.

Another question about group therapy. In a group session, would all the participants be working on the same LTG/STG during the session, or could they each be working on separate goals?

 

[DreamFreak]

Each child works on their own goals during group session. For example, one of the SP goals is to play interactively with 2-3 peers in a preschool setting w/out adult intervention...another one is to tolerate play in textured materials (i.e. popcorn, fingerpaint, etc.) without adverse response. Child #1 may be working on the first goal while Child #2 may be working on the second goal. They could both "play" in the textured material together but be working towards two totally different goals. I hope that makes sense. Another Sensory Processing goal is to play simple games with other children...so there are times when group therapy is necessary to achieve a goal for one particular child...but the time isn't wasted for the other children meaning the group "play" therapy is always tailored to meet the needs of all children involved.

 

[Beetle]

OK, that should be enough info to at least put together a basic table structure and establish some relationships. I've got a few things going on at work today so it may be Monday before I post back with some suggestions.

 

[Beetle]

I decided that it might be easier for you to understand this if I just uploaded a sample application instead of trying to explain it all in a post. This example includes tables, relationships and a few example forms to demonstrate how the flow of data entry might work with a table structure like this. This may not be exactly what you had in mind but it may at least give you some ideas that you can use to work out the problems with yours.

The two main forms in this example are frmClients and frmSessions. Each has two continuous sub forms so there are hidden text boxes on each form to help control the Master/Child links (not sure if you're familiar with that method or not). The basic idea was that each Client will have a Plan Of Care that includes both Long Term and Short Term goals. Each session will include one or more Clients and each Client in that session will be working on one or more Objectives (ST goals). I made the assumption here that the LT goals are complete as soon as all the related ST goals are complete, so during the sessions they would really only be working on ST goals (not sure if that's correct or not). Note that when selecting the ST goals a Client is going to work on during a session, you can only select goals that were "assigned" to that Client in the POC (again, I made the assumption this is something you would want).

Keep in mind that this is just a rough example. It would need some refinement if you were going to use this approach (for example, some code to prevent the selection of a ST goal before a LT goal is selected, error handling, etc.)

Anyway, have a look and post back if you have questions

 

[DreamFreak]

Holy moses...you are simply amazing!!! From what I've seen thus far, this is darned close to what I pictured.

I'll check it out more thoroughly and get back to you asap...THANK YOU - THANK YOU - THANK YOU!!! This is so much more than I ever thought I was going to get when I came to this forum...sincere handshake headed your way!!!

 

[DreamFreak]

How do I attach the database I started?!? I was wondering if you'd take a look at it and tell me how on earth you did the amazing things you did to the one you made. I thought I was pretty familiar with Access but you've proven me so very, very wrong (-: What you've created is more than what I could have ever expected...do you have an email address I could send it to or would you rather me try to attach it here?

 

[jdraw]

Further to Beetle's comments and suggestions, I would recommend your reading the first few topics at
http://www.rogersaccesslibrary.com/forum/topic238.html

The concept of Normalization is key to getting your tables structured properly. Working through Entity Relationship diagramming with your own data will give you more insight on your data and relationships than you thought possible. Until you get the underlying model correct - and by that I mean representing your business rules/facts, you can play at Access ( or any data base system ) "till the cows come home", and still be going in circles. Too many people jump into Forms and drop downs with out understanding the business issues, the "things involved" (entities) and how the data (attributes and relationships) fits together.

 

[DreamFreak]

I will definitely read what you posted above...and I agree with what you're saying about needing to understand how the business works and how the main components fit together. I'm doing the database for my sister's second job...the reason I'm doing it is because the State of Maine has made so many cuts in their budget that they are desperate to find a way to make their "note-taking" take up less time.

In a nutshell, each child is allotted "X" units of time towards their treatment and hopeful recovery. It used to be that there were enough units allotted to cover the hours of notes each child's file requires in order to CONTINUE receiving treatment. Now...after the budget cuts, employees are forced to take their work home (which in turn takes time away from their own families) and spend anywhere from 8 to 18 hours (give or take) a week hand-writing everything.

Because the cause is so great, I volunteered to do what I could on my end to come up with something to ease the burden of time. Unfortunately, I have no experience with this field and have relied heavily on my sister's input on how each component works and how everything fits together...it's still a little foggy, but it is getting clearer.

I appreciate ALL of the help I've been given thus far and hope that the link you posted will only help to make all of this a little easier.

Much appreciation to both of you!

Dream

 

[Beetle]

I was wondering if you'd take a look at it and tell me how on earth you did the amazing things you did to the one you made.

Well, that's a bit of a broad scope. There are several things going on there (in the forms) that you may not be familiar with, and, as JDraw mentioned, the most important thing right now is the table structure. That is the main purpose of the example (to give you one possible idea of how a table structure might be constructed for a model like this). I included the forms more for future reference once you get to that point, assuming that the suggested table structure will work for your scenario (I probably should have made that more clear in my previous post).

It is not only important to get the table structure right, but to understand it as well. If you don't understand how and why your tables are structured a certain way, along with at least the basic principles of proper normalization, then you will be completely lost when you get to the point of needing to create queries, forms and reports based on those tables.

Now, if you've got a specific question about something and you feel that it might be necessary for someone to see the database in order to understand the issue, then feel free to zip your db and attach it in the forum along with your question. If you ever decide to do that, please be sure to remove any sensitive data from the db before uploading it.

 

[jdraw]

For more info:

Here are a number of free video tutorials related to database design concepts.

These free video tutorials describe the processes of data modelling, normalization and entity relationship diagramming. There are other videos, but this group is by the same presenter and covers an example situation.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2