Foreign Key Issues

[Joel Maxuel]

Good afternoon,

I'm having troubles with a query I'm workiong on. I have three tables for a
patient recvoery database: Admission Table, Discharge Table, and the Domains
Table. the Discharge Table and Domains table are left joined from the
Admissions Table, so the record will show even if the other two haven't been
started yet. I could have sworn that this worked sometime earlier, but I get
one of the following error messages whenever I add data to a record:

"You cannot add or change a record because a related record is required in
table 'Admissions Table'."
"The changes you requested to the table were not successful because they
would create duplicate valyues in the index, primary key, or relationship.
Change the data in the field or fields that contain duplicate data, remove
the index, or redefine the index to permit duplicate entries and try again."

My query pretty well looks like this (removed fields to save space):

SELECT [Admission Table].ID, [Admission Table].[First Name], [Admission
Table].[Last Name], [Domains Table].ID AS DomainID, [Domains
Table].[Readiness Assessment Score], [Domains Table].[Admission QOL Score],
[Domains Table].[Outcome QOL Score], [Domains Table].ContactPsychiatristName,
[Domains Table].ContactUnitFamilyPhyName
FROM ([Admission Table] LEFT JOIN [Discharge Table] ON [Admission Table].ID
= [Discharge Table].[Assigned ID]) LEFT JOIN [Domains Table] ON [Admission
Table].ID = [Domains Table].ID;

It appears that the foriegn key in the Domains table is sticking at zero (No
matter what the Admission's table's PK value is). What am I doing wrong?
Also, I have a similar query for my discharge form that has experienced no
problem:
SELECT [Admission Table].ID, [Admission Table].[First Name], [Admission
Table].[Last Name], [Discharge Table].*
FROM [Admission Table] LEFT JOIN [Discharge Table] ON [Admission Table].ID =
[Discharge Table].[Assigned ID];

I'm using Access 2003 SP2 on Windows XP SP2.

 

[Jerry Whittle]

Your best bet is to not attempt this with a query. Instead make sure that the
relationships are formalized in the Relationships window with Referiential
Integrity enabled.

Create a form based on the Admission Table. Next create subforms for both
the Discharge and Domains tables and place them on the Admission form.

Now Access will keep the foriegn keys straight for you. For example if you
go to an admission record then add discharge information for the first time,
the form will populate the foriegn key field in the Discharge table.

 

[Jerry Whittle]

Your best bet is to not attempt this with a query. Instead make sure that the
relationships are formalized in the Relationships window with Referiential
Integrity enabled.

Create a form based on the Admission Table. Next create subforms for both
the Discharge and Domains tables and place them on the Admission form.

Now Access will keep the foriegn keys straight for you. For example if you
go to an admission record then add discharge information for the first time,
the form will populate the foriegn key field in the Discharge table.