Relationship must be on the same number of fields with the same...

[Neuro]

When I attempt to make a 1-to-many relationship between two tables, I'm
getting the error: "Relationship must be on the same number of fields with
the same data types". I've double checked that both data types are "text"
and I don't understand how they could possibly be on different number of
fields.

I have a table of names that are being called to the main table via a
combo-box. This works fine except that I cannot filter by the names in that
field within a query because I cannot "enforce referential integrity" while
setting the relationship. I have set up quite a few 1-to-many relationships
in the identical way (I think) and I don't understand why I'm getting an
error in this case.

Thanks for your help!

 

[Jeff Boyce]

Which field(s) in which tables are you using to try to set that
relationship?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[KARL DEWEY]

I have a table of names that are being called to the main table via a
combo-box.
Is the 'table of names' one of the two tables you are trying to set up in
the relationship? What is its primary key field?

 

[John W. Vinson]

When I attempt to make a 1-to-many relationship between two tables, I'm
getting the error: "Relationship must be on the same number of fields with
the same data types". I've double checked that both data types are "text"
and I don't understand how they could possibly be on different number of
fields.

Is either of these fields a Lookup Field, or a multivalued field?

 

[Paul Shapiro]

When I attempt to make a 1-to-many relationship between two tables, I'm
getting the error: "Relationship must be on the same number of fields with
the same data types". I've double checked that both data types are "text"
and I don't understand how they could possibly be on different number of
fields.

I have a table of names that are being called to the main table via a
combo-box. This works fine except that I cannot filter by the names in
that
field within a query because I cannot "enforce referential integrity"
while
setting the relationship. I have set up quite a few 1-to-many
relationships
in the identical way (I think) and I don't understand why I'm getting an
error in this case.

Did you verify that both fields are specified for the same length?

 

[Neuro]

This database is to keep track of biological experiments. One column in the
main table ("SpecimenInfoTbl") keeps track of which researcher is responsible
for a given specimen. In order to maintain data entry integrity, I have a
separate table named "ResearcherInfoTbl" that contains the name of each
researcher in the lab. ResearcherInfoTbl has two fields: 1) ResearcherID
(primary key; AutoNumber) 2) Researcher (text).

The SpecimenInfoTbl looks-up the ResearcherInfoTbl via a combobox. This
actually works and I can even query the results. However, the query itself
cannot be filtered by the name of a researcher (no results come back).
Normally, when a relationship isn't set up with enforced referential
integrity, I can at least filter using the unique AutoNumber from the primary
key; however, this isn't the case here. Ideally, I would like to be able to
establish the 1-to-many relationship with enforced referential integrity so
the researchers using the database can easily filter the data by their name,
but I am getting the error, "Relationship must be on the same number of
fields with the same data types."

Thanks again!

 

[Neuro]

Both fields have 'field size' set to 255 characters and both 'allow 0
length'. These setting are in the 'General Tab' for the field. Is there
someplace else that I should be looking?

 

[John W. Vinson]

This database is to keep track of biological experiments. One column in the
main table ("SpecimenInfoTbl") keeps track of which researcher is responsible
for a given specimen. In order to maintain data entry integrity, I have a
separate table named "ResearcherInfoTbl" that contains the name of each
researcher in the lab. ResearcherInfoTbl has two fields: 1) ResearcherID
(primary key; AutoNumber) 2) Researcher (text).

The SpecimenInfoTbl looks-up the ResearcherInfoTbl via a combobox. This
actually works and I can even query the results. However, the query itself
cannot be filtered by the name of a researcher (no results come back).

Exactly.

You're another victim of Microsoft's misleading, misdesigned, inadequate, and
altogether infuriating Lookup Wizard. The table APPEARS to contain the
researcher name, and - in the *very* limited circumstances envisioned by the
Lookup Field designers - even lets you search by that text.

But it *doesn't* contain the name. It contains the numeric ID.

Normally, when a relationship isn't set up with enforced referential
integrity, I can at least filter using the unique AutoNumber from the primary
key; however, this isn't the case here. Ideally, I would like to be able to
establish the 1-to-many relationship with enforced referential integrity so
the researchers using the database can easily filter the data by their name,
but I am getting the error, "Relationship must be on the same number of
fields with the same data types."

I gather that you're trying to join a text field containing the researcher
name, with this lookup field in SpecimenInfoTbl? If so you're trying to join a
Text to a Long Integer (a long integer that's concealed from your view of
course), which you obviously cannot do.

You need to base your query, not on SpecimenInfoTbl, but on a Query joining
SpecimenInfoTbl to ResearcherInfoTbl. The criteria can then be applied to the
name field (in the Researcher table).

 

[Neuro]

That explanation was very helpful. I simply changed the Researcher field in
the SpecimenTbl from 'text' to 'number' and everything now works perfectly.

 

[Paul Shapiro]

Maybe try simplifying while you identify the problem. You could import just
those two tables into a new blank database. Delete all the data in both
tables. See if you can create the relationship and then work your way back
towards the full case.

What Access version are you running, and which db format? I haven't heard of
any Access bugs in this area, so it could be that the error message is
mis-directing your attention, but I would guess there probably is a logical
problem. If you haven’t done so already, you could make sure the latest
Office service pack is installed. Maybe try another computer if you have one
available. If all else fails, you could zip up the test db without any data
and just those two tables and email it to me along with the field names that
should participate in the relationship. Sometimes a 2nd pair of eyes sees
something different.

Also, the presence or absence of a relationship does not affect what you can
do in a query. When the relationship exists, Access assumes you want to join
the two tables using the relationship and automatically creates the join for
you, but you can do the same thing without a relationship. Drag the field
from one table in the graphical query designer and drop it on the matching
field in the other table.