Error trying to set up a relationship

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alex - 13 Jul 2005 15:30 GMT

I have two tables where one of the table's data is supposed to be a subset of
the other table. In my particular case, the main table contains Specialties
(these are medical specialties, as in Cardiology, Dermatology,
Endocrinology, etc.). The other table contains a subset of these, which are
the specialties that an insurer is supposed to cover. Both tables have a
RelationshipID column (this column is the primary key of the main table).
While I can link the two tables in the Relationships window, Access doesn't let
me enforce referential integrity. Is that the way it's supposed to be, or is
there something that I am missing? I want to avoid a user being able to enter
a Specialty in the subset table which doesn't already exist in the main table.

Reply to this Message

Lynn Trapp - 13 Jul 2005 15:51 GMT

Are these linked tables?

Signature

Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

>I have two tables where one of the table's data is supposed to be a subset
>of
[quoted text clipped - 13 lines]
> a Specialty in the subset table which doesn't already exist in the main
> table.

Reply to this Message

Alex - 13 Jul 2005 17:36 GMT

No, the tables are not linked. I created the tables in Access. What I find
confusing is that I can do a query with an outer join that shows me the rows
where the subset data doesn't have a corresponding record (as it should be). I
don't see anything wrong with the data per se.

> Are these linked tables?
>
[quoted text clipped - 15 lines]
> > a Specialty in the subset table which doesn't already exist in the main
> > table.

Reply to this Message

KARL DEWEY - 13 Jul 2005 18:58 GMT

Sounds like a user has entered a Specialty in the subset table which doesn't
already exist in the main table. Run an unmatched query and review the
results. Remove the error or change the unmatched query to an append query
to build records for what is missing.

> No, the tables are not linked. I created the tables in Access. What I find
> confusing is that I can do a query with an outer join that shows me the rows
[quoted text clipped - 20 lines]
> > > a Specialty in the subset table which doesn't already exist in the main
> > > table.

Reply to this Message

Alex - 15 Jul 2005 14:56 GMT

> Sounds like a user has entered a Specialty in the subset table which doesn't
> already exist in the main table. Run an unmatched query and review the
> results. Remove the error or change the unmatched query to an append query
> to build records for what is missing.

That would be the obvious answer, but that wasn't it. I ran unmatched queries,
and the results came out as expected. I am not sure what it was, perhaps
corruption in an index, but I was eventually able to set up the relationships.

Thanks to all.

Reply to this Message