Error trying to set up a relationship

[Alex]

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.

 

[Lynn Trapp]

Are these linked tables?

--
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

 

[Alex]

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.

 

[KARL DEWEY]

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.

 

[Alex]

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.