index or primary key cannot contain a null value error?

[whoknows361]

I have 3 tables:

Client_Data tbl
Client Name PK
client phone
client address
etc.

Open_Episode tbl
Client Name PK
Admit Date
suicidal? (yes/no)
homicidal? (yes/no)
etc.

Diagnosis_history tbl
Client Name PK
Axis 1 Diagnosis
Axis 2 Diagnosis
Axis 3 Diagnosis
Axis 4 Diagnosis
Axis 5 GAF score
Date Diagnosis assigned

I have 2 forms. The first form "referral_form" adds all the data to the Client_Data table(no problems)
My second form is called Open_episode and is used when a Therapist actually opens the client from the referral.
Part of this form adds the admit date, suicidal/homicidal info to the Open_Episode tbl, and part adds the diagnosis information to the Diagnosis_history table.

On this second form i use a list box so the user can chooose the client's name(populated from the Client_data table, Client name field), and then the user can enter the information that will update the Open_Episode and Diagnosis_history tables. However when I submit with an add new record button it gives me the "index or primary key cannot contain a null value" error.

my thoughts: is this happening because there are no client names in the Open_episode and Diagnosis_history tables yet?

Would appreciate any advice.
This is my first big Access creation, I have read the normalization and Form 1-3 reads... but was wondering what I was doing wrong here. I think I'm missing a step.

Thanks in advance
Jonathan
I can provide you with what I currently have.. Its access 2007.. Let me know if you need it to solve this problem

 

[dhookom]

I wouldn't go any further until I changed the table structures (I think you have been told this already).
1) I would not use Client Name as primary key since names are not always unique.
2) Suicidal and Homicidal are data values and should not be field names. If you want to track these type of attributes, you should create a related table with individual records for Suicidal, Homicidal,...
3) Your diagnosis table IMHO is also wrong. Each diagnosis should create its own record in a related table.

There are lots of great resources to study normalization.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Have a look here:

http://r937.com/relational.html

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

In another thread thread700-1507909, fneily offered an alternative data structure for this application. It is clear fneily spent a fair amount of time creating his recommendation. There was no response to fneily's post, just a new thread with an old structure. His post even included the same r937.com link.

Duane
Hook'D on Access
MS Access MVP

 

[whoknows361]

I was using fneily's assistance as I have readdressed my table structure, and thought I was onto solving the problem I was having, hence the question above. I did read the prior link, you will have to forgive me, but this is my first project and I thought I had created a solution.

Now, I guess the best way is to break my tables down into their smallest structure as has been suggested. The way I was attempting to do it is to have a table for each "event" which occurs linearally when a case is seen. IE. referral turned in, open episode completed, 6 mo update completed, client discharged. But it seems that this will not work this way. Perhaps that is the primary problem is that I'm trying to create a table structure around linear events.

Thanks for the help, I'll be restructuring.