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

 

[himdata]

Hello again

I have been working on the Db and background reading, which has confused me! I should have posted back here sooner but am rather embarrassed not to have been able to work out what I am doing.

To recap. the db is to collate info from various assessments that clients undergo. The data will now not have anything to identity the client and the info relates to that delivery only. Previous or subsequent deliveries are not relevant.

The current situation is:

I have these tables

tblCase (this contains data relating to that delivery only)
CaseID, PK auto
MothersDateofBirth
DateOfDelivery
etc

tblQuestionsAsked
QID PK auto
Description

tblEncounters
EID PK auto
Description

tblAssessments
AID PK auto
CaseID FK
EncounterID FK
DateOfAssessment
ParkynScore
LarsonScore
EPDSSCore
Risk
Consent
etc

tblResults
RID PK auto
AID FK
QID FK
Answers

tblAnswerType
ATID PK
RID FK
AnswerVelue (number, 1, 2 0)
Text (yes,no, unknown)

I have a form to enter the data using Tbl case and a subform of tblAssessments. I have tried to enter dummy data but get the error message "you cannot add or change a record as a related record is required in tblEncounter.

The relationships are all one to many with referential integrity and cascade update.

So, I am sorry to ask again, but am going round in circles.

 

[fneily]

Referential integrity - in keeping with the theme - means you need a Parent record for every Child record. You can not have orphens. So if you try to create a child without a parent, you get an error. So you're trying to put in an EncounterID that's not in tblEncounters. And the reason is obvious - you have EncounterID in one table and EID in the other. Try not to do that. Keep names the same.
Also, there's no reason you should have Cascade Update on. That's only to do massive changes to both Parent and Children.
On the form for tblAssessments, CaseID and EncounterID should be comboboxes where you just select the data.

 

[himdata]

Thanks for replying.

That was a typo error in my post, they do have the same name. I will remove the cascade update option and see where that gets me.

 

[himdata]

Its 10pm and Its obvious that I am out of my depth here and will have to admit defeat soon and say I cant do this project. I have attached a link to the DB if you would look at it please.

The encounterID was the same name, as access won't let you create a relationship unless they are.

Which subform should I have to the case form? I am not sure what you mean by the combo boxes either. When removed the encounters table from the design I could enter test data but the subform moved to the next form, but the main form stayed with the same CaseID. So there must be a link somewhere that I havent put in.

I keep making changes and adding and removing tables, but I have lost the plot completely.
Thank you again

 

[fneily]

The encounterID was the same name, as access won't let you create a relationship unless they are." Not quite. You would have to manually create the linkage.

You do not have EncounterID on your tblAssessmentDates Subform. Thus the error.

Don't create any relationships in the Relationships window until you know what's going on.

When you put EncounterID on the tblAssessmentDates Subform, you can make it a combobox. The goal is to reduce actual typing. No typing, no typing errors.