changing relationship type 2

[himdata]

I have a database with one main table containing demographic information of each client with UniqueID as the primary key, then 6 other tables containing data gained from interviews with the clients at different times. Unique ID is the only identical data in each table. I have these set up with one to one relationships with the demographics table. yesterday I added a 7th table but I cannot set the relationship as one to one, it comes up as one to many.

I apologise for being an idiot and asking a stupid question. I am hoping that someone here will be patient enough to help me!

Many thanks

 

[fneily]

Here's some reading that might help you:
Fundamentals of Relational Database Design

http://r937.com/relational.html

You keep adding tables. You have tables tracking the same overall item. That's a sign that something is wrong with your database design.

Maybe you could post your table structures, eg.
tblClient
ClientID
Firstname
LastName

tblQuestion
QuestionID
etc.

One-to-many means UniqueID is not unique in a table. Can't tell without seeing your table structures.

You may want to search the FAQ's of the Access forums to see how to design a "survey" database.

 

[fneily]

Do a search on this forum on the word Survey. You'll see other questions concerning surveys. Here's one:
thread700-1447289

 

[himdata]

thank you for the replies.

Since I posted I deleted the table and made a new one, this has a one to one relationship. I don't know what I did differently,though I must have for there to be a difference.

I have read the links and advice you kindly gave me.

My DB structure was checked some months ago my my IT tutor, but I am no longer at college and no longer in contact with the tutor.

my tables are
tbl Demographics
Unique ID PK
Date of birth
Postal Code
etc

Then each other table relates to an existing paper form used to get information from each client about their state at different times.

tbl1
Unique ID Fk
ID PK
Date of info
Risk Score

tbl2
Unique ID Fk
ID PK
Singleton Birth
Score
Multiple Birth
Score2

tbl3
Unique ID Fk
ID PK
date of questions
Mental health risk
etc

My forms are as suggested in the links, with a demographic top form and then a subform for each "survey"

I found the links interesting as I had never considered the project as a survey or questionnaire.

You are suggesting I change my tables to be
Demographics
Survey names
Questions
Answers

Do I understand you correctly there?

Many of the answers are Yes, No or unknown, which require a numerical answer for statisical analysis. I have used option
groups to acheive this

thanks again

 

[fneily]

A couple of questions:
Does Singleton Birth and Multiple birth apply to the client? Or is it how many the client had? By the way, tbl2 is not designed correctly, but I need the above answer to fix it.

What's the difference between DateOfInfo and DateOfQuestion?
What's the difference between RiskScore and MentalHealthRisk?

Also, it's better if you don't use spaces inbetween words. If you must, use a _ such as Date_Of_Birth.

Are these your only tables, or do you have tables storing the questions and answers?

 

[himdata]

Singleton birth and Multiple Birth do apply to the client, for this delivery only.

Each table represents a new mothers point of contact with a nurse. The nurse asks the mother the questions on the paper form she has to use for that encounter. The nurse sometimes makes an assessment of the client and records this. Sometimes the answers are yes/no/unknown. or are numerical.

for tbl1 DateOfInfo is the date of the first encounter
tbl3 has a different date and this needs to be recorded.

Each encounter is a different way, or different timing of recording the new mothers risk of developing depression.
I also need to record whether consent has been given by the client.

I have 7 tables each representing the questions and answers for each encounter.

After reading your links I have been trying to address how to have one table for Q and one for Answer. I'm sorry to say that I can't grasp this concept. Surely if you have one field asking the risk level then you have another field to place the answer??

Again, thank you for taking the time to reply. I very much appreciate it.

The DB I have seems to work with the dummy data that I have put into it but some of my queries and reports are clumsy.

 

[fneily]

So DateOfInfo is first encounter's date and DateOfQuestions are other encounter dates?
Does a client give consent up front or must give consent for each encounter?
So an encounter is basically a survey?
Does a nurse make an assessment for each encounter or when all encounters are finished?
What are the scores for the births mean?

Getting there.

 

[himdata]

HI
Yes, those are separate encounter dates.

The client gives consent at different stages, ie to a home visit. (The legal side of this is being investigated to this may change)

No,some encounters are based on a nurses observations and are recorded in the clients chart immediately, others are scored according to answers given by the client. Only the total score is recorded in the database. Sometimes the answer to a question may be a yes, and this yes response indicates risk of depression.
The database wont be populated with a clients data until all the surveys are done.
The scores are for risk again, there is one for each baby, but it relates to the mother.

In the meantime I have started a new DB, with a demogaphics table, a Q table and an answers table. Though the Q and A tables are identical. I have also made forms for demo and each survey.

Continuing thanks!!

PS, am working in Access 2000

 

[fneily]

Ok. We'll see if I get close to understanding everything. There will be 4 main tables:

tblDemographics
ClientID Primary Key
FirstName
LastName
DOB
Address
City
State
PostalCode
Phone
etc.

tblNurse
NurseID PK
FirstName
LastName

tblQuestions
QuestionID PK
Description

tblEncounters
EnconterID PK
Description

Now clients have babies so:
tblDeliveries
DelID PK
ClientID FK
Male/FemaleOfNewborn (M/F)
Score
DateOfDelivery

An assessment brings together the client, nurse, which encounter the client's on. Need to create an Assessment table with those and any common fields.
tblAssessments
AssessID PK
ClientID FK
NurseID FK
EncounterID FK
DateOfQuestions
EncounterType (home, office, parking lot)
Score
RiskType (None, Maybe, Suicidal)
Consent (Y/N)

Need to keep track of answers for each question of each encounter so:
tblEncounterAnswers
EAID PK
AssessID FK
QuestionID Fk
Ans (Y/N)

So for an Assessment, the last table shows which questions were asked and stores the answers. It also can match up the questions for which encounter.

By the way, your IT tutor doesn't know how to normalize.
See:
Fundamentals of Relational Database Design

http://r937.com/relational.html

 

[himdata]

Hi again and thanks

To clarify some points. We dont need to know which nurse, or where. The data will be used for research not client care.
Demographics does use name and address. I had included details of the babe in there as the UniqueID just relates to that delivery. A mother could have a second delivery a year later, the UniqueID would have a different suffix. We dont need sex of baby. Just date of delivery.

So,how would this work?

tblDemographics
UniqueID PK
PostalCode
FamilyDoctor
DateOfBirthMother
DateOfDelivery
Singleton
Multiple


tblQuestions
QuestionID PK
Description

tblEncounters
EnconterID PK
Description

tblAssessments
AssessID PK
UniqueID FK
EncounterID FK
DateOfQuestions
Score
RiskType (None, Maybe, Suicidal)
Consent (Y/N)

tblEncounterAnswers
EAID PK
QuestionID Fk
Ans (Y/N)

Or am I not grasping this at all?? I think I am.

I agree with you re tutor, a nice man but confusing and vague.

 

[fneily]

I could disagree about keeping track of the nurses. They are the ones collecting the info and they can affect the research. But that's up to you.

Read the reference about normalization. VERY important. A client can, as you stated, have more then one baby. That's a one-to-many relationship. So deliveries are not in the demographic table. Thus I have tblDeliveries. No baby/delivery info in tblDemographics.

I would disagree that the sex of the baby is not important. According to studies from Johns Hopkins Hospital, Mass. General, Stanford, etc. mothers may react differently depending on what sex their baby was. If not what they want, they get depressed. But that's up to you.

I think you should connect the Assessment to the questions. As a researcher, I would want to know when the questions were asked and to which client. But that's me. So in tblEncounterAnswers, put AssessID or UniqueID.

I don't know the demographics of the women you're studying. But I worked for my local Narcotics Unit in the 90's and met women that were depressed because they didn't want to have a baby because it interfered with their partying. You might want to include questions like that in your study.

 

[lespaul]

And I would much rather capture too much information than not enough....you're told now that the sex isn't important, but in 6 months SOMEONE will say, "I wish we could see information on the sex" and you won't be able to because you didn't capture the data...much better to capture it and be the hero and in 6 months tell them "WE CAN!"

If there's information, capture it. If you aren't using it now, then there may be a future use, if it turns out to never be used, well storage space is cheap.

Leslie

Have you met Hardy Heron?

 

[himdata]

Hi,I would agree with your points about what to include in the study if we were studying PPD itself, What we are looking at tho is the effectiveness of the current assessment methods in identifying PPD.

The uniqueID relates to that delivery only not to the client alone, its a combination of the client and babe(S)delivered at one time. Perhaps I should change the table title to Delivery.

I am reading the article on normalisation right now,but in the meantime I have this.

tblDelivery
UniqueID PK
PostalCode
FamilyDoctor
DateOfBirthMother
DateOfDelivery
Singleton
Multiple

tblQuestions
QuestionID PK
Description

tblEncounters
EnconterID PK
QuestionID FK
Description

tblAssessments
AssessID PK
UniqueID FK
EncounterID FK
DateOfAssessments
Score
RiskType (None, Maybe, Suicidal)
Consent (Y/N)

tblEncounterAnswers
EAID PK
QuestionID Fk
Ans (Y/N)

For data entry I have a form for delivery and one for each encounter/assessment. I will make these subforms to the delivery form, with parent child relationships. Will this ensure that the Unique ID need only be entered in the Delivery form?

Thanks again, you are very patient!

 

[himdata]

I have been reading thread on questionnaires and the normalization document. Sometimes I think I have had a eurek moment and then it goes away again.

Do I need a tbl that gives answer codes where the assessment answers are yes/no/maybe? We need the answers in numeric form for analysis. In my original database I used an options box in forms.

I am confused over why two tables for assessment and encounter answers, would one table called answers cover this? There would be no duplicate information.

tblDelivery
DeliveryID PK
PostalCode
FamilyDoctor (Yes/No/Unknown)
DateOfBirthMother
DateOfDelivery
Singleton
Multiple

tblQuestions
QuestionID PK
Description
So this would be a tall thin table with a row for each question?

tblEncounters
EncounterID PK
Description
Another tall thin table?

tblAssessments
AssessID PK
DeliveryID FK
EncounterID FK
DateOfAssessments
Score
RiskType (Yes, No, Not assessed)
Consent (Y/N)

Table would look like this?
AID DelID EncID DteOfAst Score RiskType Consent
1 203 30 10may08 13
2 323 31 2feb08 yes
3 243 32 1jun08 4 yes

tblEncounterAnswers
EAID PK
QuestionID FK
Ans (Y/N)

 

[fneily]

So this would be a tall thin table with a row for each question?" Yes.

Yes. You can have a little table like:
tblTransform
AnswerID AnswerNum
yes 1
no 2
maybe 3

Even if you create an option group, somewhere down the road you will want to know what a 1 means. etc.

Why two tables? Maybe for now, such and such questions are asked in this encounter. But maybe in six months, someone wants to change which questions are asked when. Now your stuck. It helps to design for flexibility. My opinion.

Your table looks right. All the fields relate to that specific assessment.

You still haven't connect the answers to a client yet, though.

LesPaul is right.

 

[himdata]

Thank you both for your help and advice. I really do feel that I have learned much here.

I take your points about flexibility for the future.

So, now I feel a load has been lifted, and will get on an build this thing

Thanks!

 

[fneily]

An aside, reason for a separate post. I feel the study you are doing is important. I actually know a woman that for some reason decided their depression was due to the baby and eradicated the child. Their serving time now.
To me, there some fundamental problems with your study.
There is a premise that the clients won't lie. This you can't test. You're basically taking their word as truth.
There can be bias from nurse to nurse. You must have consistency between the nurses. Nurses can have different viewpoints skewing the results. You can't test for that. That's why you should track nurse data.
The above examples introduce alot of subjectivity.

The main problem is that after the data is collected, and some statistics are ran on it to make the project look good, how it'll be analyze. I bet it'll be by what's known as Diagnosis by Consensus. A group of researchers get together and by acclamation reach a diagnosis. This is, bottom line, unscientific. You've heard of ADD, ADHD, Bipolar. These are not scientifically proven ailments. The diagnosis for these are just observations, such as what you're doing. Some researchers at NIH and Johns Hopkins have doubts they even exist.
If you want a real-time example of what I mean, see the International Psychogeriatric Association website at

http://www.ipa-online.org/.

Look around for their article On Detection and Diagnosis of Delirium in the Elderly. The "study subjects were 87 patients aged 65 and over.". "A consensus conference, attended by the three psychiatrists and the nurse clinician, used all available information to reach a consensus diagnosis."
I'm familiar with Alzheimer's from my caregiving of my 92 year old mom for the past 8 years. I have seen others with Alzheimer's exhibit the same external behavior as someone with Delirium. So the "Consensus Diagnosis" would have been wrong. But in the study, they never even considered it.

Also, whatever their conclusions, it would be interesting to see how they distinguish if the behavior observed by the nures or reported by the client is a symtom of depression or the cause of depression. As a reference, no one to date knows if "ADHD" is a cause or symtom.

And if the researchers are not doing any drug/alcohol testing twice a week on the clients, then I would question any results they get.

They'll probably end up concluding:
Motherhood, especially after birth, is stressful.

Just discussion points.

 

[himdata]

Hi
Thanks for taking the time to raise those points. I will discuss them with the scientists who are carrying out the study.


I apologise for not replying sooner but I have been unwell, so am now back at work and going to attack this database.

 

[himdata]

I had intended to send you a PM to discuss this further but I can't find a way of doing that. Perhaps this forum doesn't support that. I cant reply in more detail without breaching confidentiality rules.

One of the reasons for the database is to see which assessment method, and the timing of that assessment, identifies the most cases. So the nurses assessments will be viewed alongside the clients self assessment.

I can see that you are concerned about the subject. I am working for people with integrity who will apply rigorous principles to the project.

 

[fneily]

Yeah, Access isn't a place to hold this type of discussion. I don't doubt their integrity. But even scientists can become myopic in their research.
As far as discussing my concerns with the researchers, might not be such a good idea. Usually, they don't like "underlings" questioning their abilities.