Constraint Clause on Relationships

[Kim]

Hi,
I'm trying to build a relationship between two tables where the primary key
in the main table is an auto-number, and the field in the (should be) related
table is a look-up column with a query as the row source (which does include
the auto-number primary key field). But I keep getting an error message that
says, "Relationship must be on the same number of fields with the same data
types." How do I fix this without losing my look-up column?? Please help.
Thanks,
Kim

 

[Jeff Boyce]

Kim

From your description, it sounds like you have a table with a lookup-type
data field. If you are aware of the confusion this data type can cause and
have designed ways to insulate the user from those confusions, and have
documentation for yourself (and for anyone else who might need to work
"behind the curtain" on this application), then you probably don't need to
consider changing the lookup data type to the underlying field data type.

If you aren't certain on all of these, and if you/your successor/your users
will be working directly in the tables, think twice about using the lookup
data type (in the table) -- the alternative, to gain the advantage of
lookups, is to use forms and combo or list boxes. Besides, Access tables
are designed to store data, forms (and reports) to display it.

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Pat Hartman]

to emphasize what Jeff said, the way to fix the relationship is to remove
the lookup at the table level. Table level lookups are a crutch for novice
users who are not able to create queries. You seem to have progressed to
that point and beyond since you are attempting to create relationships.
Table level lookups work well in one and ONLY one case which is when the
lookup table is a single column and the visible value of the combo is the
primary key of the lookup table. So if your lookup table contained Red,
Blue, Green and that's what your combo showed, things would work swimmingly.
However if your color table had a ColorID as the Pike and the color names
were the visible values, the table level lookup becomes a problem waiting to
happen as you have discovered.

Since we NEVER give the user a table or query to use as a data entry tool,
there is no benefit to using table level lookups. Leave the lookups for
forms where they belong. Create queries for yourself if you want to see the
text values alongside the numeric ones.