table design is confusing me.. please help me clarify

[whoknows361]

hello all. firstly i appreciate ur help on my last 2 questions. I have a more effective design based on your support, but at this point I am a bit confused.
background: i am creating a database for program managers in a mental health facility. my current problem is how to arrange the diagnosis table(s).
current: i have created tables to handle client data, changes in therapists and case managers, 6 mo and annual updates to information, discharges and referrals, but am not sure how to arrange my diagnosis information.
info: A client is given a diagnosis on 5 axis' at certain points in their treatment. Once at open, second at 6 months, then at 12 months and then at every 6 month interval. Additionally, A therapist can change the current diagnosis on any axis at any time in treatment, until their discharge.
my thoughts: I was thinking of creating a table like this:
DX TABLE
Client name pk (links to client data table)
initial axis 1 code
initial axis 2 code
initial axis 3 code
initial axis 4 code
initial axis 5 score
6 month axis 1 code
6 month axis 2 code
6 month axis 3 code
6 month axis 4 code
6 month axis 5 score
12 month axis 1 code..... etc (for up to 5 years)

but am running into a conceptual understanding of how to include the dates, codes and score for each axis if a therapist changes the diagnosis anytime in treatment.

my ultimate goal is to be able to pull up in a report the dates and 5 axis of diagnosis assinged throughout a ct's treatment. ie.. when and what diagnosis was assigned to each axis. I have attached my current database design in hopes of some support.. and any ideas in regards to database design would be appreciate as well.
This is my first big database design and usage of Access and I would apppreciate all comments and ideas in how to do this and how to make my overall project more effective. Thanks for your help in advance.

If there is any more information you need for an effective response, please let me know and I will provide it.

Jonathan

 

[dhookom]

There are lots of good web sites and other resources that discuss normalization. Find one or more and read them. I'm not quite sure where to start but any time you have multiple numbered field names, it is almost always the wrong structure.

I don't know what you mean by "5 axis'" but I would store a single diagnosis in a single record. If a patient had 4 diagnosis, this should create 4 records.

Duane
Hook'D on Access
MS Access MVP

 

[fneily]

Here's a standard bit of reading:
Fundamentals of Relational Database Design

http://r937.com/relational.html

Also, don't assume everyone has Access 2007. I can't open your database 'cause I use Access 2000.

So, I'd have something like:
tblClient
ClientID PK
Firstname
LastName
Address
Street
Etc.

tblCodes
CodeID
Description

tblTherapists
TherapistID
Firstname
Lastname
etc.

tblCaseManagers
CMID
FirstName
LastName
etc.

Those are my main tables. Now to connect them.
tblClientTherapist
CLTID
ClientID
TherapistID
AssignedDate
etc.

tblClientCaseMan
CLCMID
ClientID
CMID
DateAssigned
etc.

You need a table to track clients entrance into the program and their discharge, referrals. One-to-many relationship to tblClient since they may repeat treatment.
tblClientDates
CDID
ClientID
InitialDate
DischargeDate
Referral

Now you must track the 5 diagnosis. Two tables:
tblDiagnosis
DiagID
ClientID
DateOfDiag

tblAxis
AxisID
DiagID
Code
Score

A one-to-many relationship between tblDiagnosis and tblAxis. So for a diagnosis on a specific date, they'll be 5 records in tblAxis.
You can find the therapist for each date by connecting tblDiagnosis to tblClientTherapist on ClientID.

6 months and annual update times are not stored. They are calculated through queries. You have the initialdate then just add.

Now this assumes I understood what you want.

 

[whoknows361]

Yes, thank you for the time placed in this table structure. It has assisted me with my table structure and conceptualiztion for some other ideas I have. Only, if I understand correclty, in tbleCodes I would have to enter all the codes and their related descriptions. There are literally thousands of these, and they change every couple years. Is there a structural way to get around this issue?

Thanks again

 

[dhookom]

The codes are all probably stored in some system. I have found them in Excel or web pages or Word documents or other databases. It is fairly easy to transfer and update values in an Access MDB. Surely you must have had some type of lookup for these codes in your existing tables? For accuracy and efficient entry, these are almost always based on tables.

Duane
Hook'D on Access
MS Access MVP

 

[lespaul]

There are literally thousands of these, and they change every couple years.

The beauty of a well designed relational database system is that it doesn't matter if the code is changed, just add a new one with the new description. Since you are using an ID field for the code description, the old records will have the description in use when they had to use it and the new records will have the new description.

And like Duane said, you will probably find a way to be able to import most of those codes from other file types.

Leslie

Have you met Hardy Heron?

 

[whoknows361]

fneily

just a question on the table design above.
I understand your main tables.. in regards to the tables to connect them. then i understand connecting the tables through the tbls ClientTherapist and ClientCAseMan.

I am a little confused on hte dx tables:
so for tblDIagnosis there is an autonumber for DiagID? or is it the diagnosis code? then this table simply stores the Client ID and date any diagnosis was assigned? correct?

and tblAxis:
is AxisID the 1-5 axees? or an autonumber?
code is the from CodeID from tblCode?

just a bit confused on how this works.. would someone mind clarifying this for me? again sorry.. my first big access project.
Thanks in advance as always

Jonathan

 

[fneily]

CodeID is from tblCodes or whatever table you get them from as explained in the other posts.

As I understand it from your first post, after 5 axis(whatever that means) a diagnosis is made. So you'd have a tblDiagnosis
DiagID Primary key (can be autonumber if you want)
ClientID
DateOfDiag
Conclusion (I guess a memo field. I don't know)

tblAxis(this tracks the 5 axis on which the diagnosis is made)
AxisID PK (can be autonumber or whatever you want)
DiagID
Code
Score

So a one-to-many relationship between tblDiagnosis and tblAxis. One diagnosis record will have 5 axis' records.

 

[whoknows361]

Maybe clarifying the axis diagnosis sys might help.
Each mental health diagnosis has 5 axis.
axis 1: mental health diagnosis (ie. major depressive disorder)
axis 2: any personality disorder
axis 3: any medical condition
axis 4: any psychosocial stressor
axis 5: is an overall score. (only one score)

each axis can have multiple diagnosis for any client at any one time. ie.. a client can have an axis 1 depressive disorder as well as an axis 1 anxiety disorder.

it is the same with each axis (except axis 5)..

How would this affect the table structure u have above?

 

[whoknows361]

oh yeah.. one more note.

these 5 axis diagnosees occur at client open, and then every six months after..

and can also occur at any time the therapist decides to changes the 5 axis diagnosis.

 

[fneily]

Ok. See I have no idea what you are working on. I am not in your field. This is the way I understand your explanation:
Every six months a client, on a specific date, gets 5 "axis" observations. Then a diagnosis is made taking all 5 conditions into account.
So on date 12/1/2008, there will be 5 records for a client and one diagnosis.
All I see is 5 records. You see Axis 1, Axis 2, etc. If you want to "name" them then just add a AxisName field. AxisID is for the record.
For a client, you can have as many dates you want, each with 5 ,or whatever, axis records.

This is what you do: Walk away from the computer. Get a pencil and paper. On a piece of paper, write down a table with data. Each separate piece of paper is a table. Now, by hand, actually go through and connect the data in the tables. This can clarify how everything is working. Trying to visualize it in your head or on the computer is difficult if this is your first time in Access.

By the way, notice that most of the examples you gave to be diagnosed is observational and not scientific? A major flaw of psychology.

 

[whoknows361]

Thanks for your continued help. I'm sorry but I think I was not clear in explaning this:
Each client has multiple diagnosees, each represented on a different axis (1-5). (axis 1 for mental, axis 2 for personality, etc.)Not just one sole diagnosis.
And each client can have more than one diagnosis on any particular access (except the last one number 5).

I did waht you said in regards to walking away from the computer to identify which pieces of information I need and took a stab at it. What about this:

tblDiagnosees
code
name
(this table would simply be filled with every code and corresponding name of all mental health diagnosees, which will be used later as a drop down box for other tables)

btw Dx = diagnosis

tblCltDiagnosees
CltDxID PK (autonumber)
ClientID (connects to ClientID from previous post)
Date of Dx
Conclusion (memo field)

tblAxis1
CltDxID
code1 (in a drop down box would be filled with the codes from tblDiagnosees)
name1 (in a drop down box would be filled with the names from tblDiagnosees)
code2 (same)
name2 (same)
code3 (same)
name3 (same)

(I want to have up to three diagnosees per axis)

tblAxis2
CltDXID
code1 (same)
name1 (same
code2 (same)
name2 (same)
code3 (same)
name3 (same)

and to do this for all five axis.. namely have tblAxis 1 - 5 - and have one to many relationships from tblCltDiagnosees to each tblAxis.

Now per your posts, and in my mind: this means that for each CltDxID number, of which a client could have many, there are up to three dx's on each axis tbl on any given date (as identified by Date of Dx in the tblClientDiagnosees table).

This should allow me to pull up any Client name, on a report, and see the date and up to three dx on each of the five axis?

Does this table structure work in accordance with your knowledge and training? or am I still missing something?

Jonathan

 

[fneily]

You just restated your original design. Did you read the reference from my first post?
You should not have Code1, Name1, Code2, Name2, etc. Drop the numbers. You are left with Code, Name, Code, Name, etc. You can not have repeating column headings. Violates first normal form.
Same with the tables. Axis1, Axis2, etc. They all the same data. Should be one table.
Keep:
tblCltDiagnosees
CltDxID PK (autonumber)
ClientID (connects to ClientID from previous post)
Date of Dx
Conclusion (memo field)

Have:
tblAxis
ID Autonumber
CltDixID
AxisNum (combobox value list 1 - 5)
CodeID

So tblAxis look like:
1 John 1 2
2 John 1 3
3 John 1 7
4 John 3 5
5 John 4 3
6 John 4 1
Bill etc.

See? It shows ClientID and which Axis they're on and the diagnosis for that Axis. Even multiple diagnosis for a single axis.
You get the Codes name from the tblDiagnosis table. You do not restore them in another table.

 

[whoknows361]

fneily.. I totally created exactly what you have above last night, prior to your post. I understand now what you were saying all along. Sorry it took so long for me to understand. I think I get it now, and the value of the specific ID for a table when there is a one to many relationship.

Thanks for your help on this one. I'm sure I'll have more questions for you in the future, hopefully more advanced ones :0 And thanks for sticking with this one

Jonathan