Join Error

[snowboarder234]

Help please with this error message:


Goal: to create a one-to many relationship join between two tables
Error message: Relationship must be on the same number of fields with same data types.

The error message may seem self-explanatory, but, as a newbie I don't have a clue.

[RuralGuy]

What are the DataTypes of the two fields?

[snowboarder234]

Right now ALL fields in BOTH tables are text fields. However, two caveats that may be useful:
1. Both tables are imported Excel files;
2. The GrantID fields that comprise the join are numbered thus: 05_03. Could the underscore be the issue?

[snowboarder234]

Whoops: One field is a date/time field. But it's not the "common" field that creates the join.

[RuralGuy]

I don't think the underscore is an issue. Did you index the GrantID field in the 2nd table? FYI, it is always a good idea to name the ForeignKey fields the same and the PrimaryKey field it represents. It is also a good idea to name the PrimaryKey field something descriptive of the table. You have a GrantID field in both tables but they are not the same data?

[RuralGuy]

The two Join fields *must* be the same DataType.

[snowboarder234]

Still not successful. To make things simpler I created two very basic tables. Please see image linked.
I took your advice as to relabeling the foreign and primary keys. The primary keys are Long integer data types (that's autoset by the software). The other fields are all text fields. When I try to create a one-to-many join (advisors have many students), I still get the same error message.

Watched some tutorial videos over the weekend and I feel like I'm doing the join correctly in the sense that I'm dragging the primary key from the Advisors table onto the Student field in the students table. Is that correct? Although, that's where I'm really confused because Advisor_ID [primary] is a long integer data type and Advisor in Students table is a text data type. If that's the error that Access is identifying I'm flummoxed because if the primary key is not a long integer then it ceases to become a primary key, if I understand this correctly.

[RuralGuy]

The Advisor field in the Students table needs to be a LongInteger.

[snowboarder234]

The Advisor field in the Students table needs to be a LongInteger.

Sorry to seem dense -- but how does one change the type filed from data to long integer. In design view, when I use the pull down menu for field type the integer option does not appear. If I type it in I get this error message.

[RuralGuy]

You would pick Number and then select LongInteger down below.

[snowboarder234]

Well, that resolved the join error. But now I can't put text into the advisor field -- only numbers. Access 2003 was so much easier.

[RuralGuy]

Create a query that joins the two tables on the Advisor field and you can include fields from both tables as if they are in the same table. It was the same in ac2003.