Microsoft Access 2007: error creating relationships

Question

When I try to create a relationship between my tables I get a message that says-

Relationship must be on the same number of fields with the same data types. (Error 3368)

The CONSTRAINT clause you are using to create a relationship includes a different number of fields for each table or includes fields with different data types. Check the number of fields in the CONSTRAINT clause, check the data types of fields to make sure they match, and try again.

 Why? How can I fix it?

How do I make them match so that I can link Customer ID (Primary Key) in one table 1 to my Customer ID (Foreign Key) in Table 2?

Answer 1

So building on what Ken said, What DataType is the Customer ID in table 1 and what Datatype is the customerID in Table 2?

Note if you use an autonumber for the Primary key, then you need to use a Number>long Integer as the Foreign key.

Answer 2

The problem here is most likely that the two columns are not of the same data type.  A CustomerID column as the primary key of a Customers table for instance will normally be an autonumber, which is just a special kind of long integer number column in which the values are inserted automatically.  Consequently the corresponding foreign key in an Orders table for instance will be a long integer number data type, but not an autonumber in this case.

With primary and foreign keys columns like this there should be no difficulty in creating a relationship between the two tables.

For future reference, when posting a question here, please give the question a title which more specifically identifies the problem, rather than merely calling it Microsoft Access 2007.

Answer 3

The tutorials should say that the datatypes have to match. The problem is they don't all tell you that some datatypes have an underlying type. For example: a datetime field is really a Number>Double. And the aforementioned Autonumber is a Number>Long Integer underneath.

Answer 4

Thank you for your Help Scott.  None of the tutorials say anything about making the foreign key a Number Type field.

My tables now have a relationship  :)  Thank you again.