What are the reasons for Unmatched Fields

[Loren Anderson]

Using the "Find Unmatched Query Wizard", I was able to
prove that, according to Access, all of the 16 foreign
key field data did not match any of the primary key data.
Visually, the data did match, since the Episode (foreign)
table used a Lookup Field to get the production name from
the Production List (primary) table. The only difference
that I could find in the Field Properties between the
primary and foreign key fields was that the foreign key
field used a Lookup Combo Box to get its data.

To fix this, I deleted the foreign key field column from
the Episode table, recreated it and manually entered the
production names. Now, I was able to enforce referential
integrity, and the query noted above retuns zero records.

So, there must have been some technical difference in the
field setups that caused Access to treat the two fields
as if they were unmatched. I have another foreign key
table that uses a Lookup Combo Box to get its data from
the Production List (primary) table, and referential
integrity works there just fine, so I don't think the
Lookup Field is the problem.

Here is my question:
I am still wondering if there is an automated way to get
Access to report the specific reason(s) for pronouncing
fields as "unmatched" and denying enforcement of
referential integrity. So far, Access is like a judge
that announces a conviction without specifying the
criminal charges.

 

[Steve Schapel]

Loren,

I think you will find the answer here...
http://www.mvps.org/access/lookupfields.htm

 

[John Vinson]

Visually, the data did match, since the Episode (foreign)
table used a Lookup Field to get the production name from
the Production List (primary) table. The only difference
that I could find in the Field Properties between the
primary and foreign key fields was that the foreign key
field used a Lookup Combo Box to get its data.

This is the primary reason that most of the MVP's here DESPISE "Lookup
Fields".

The Episode table *appeared* to contain the production name. BUT IT
DIDN'T. It contained the numeric foreign key to the lookup table, and
that simple fact was concealed from your view by Microsoft's
misdesigned, misleading, obnoxious Lookup misfeature.

I would strongly suggest that you should store the numeric
ProductionListID in both the Episode table and this table, and use
Queries or combo boxes on Forms (*not* table lookups!) to display the
name while storing a numeric ID.