Access creates "Indeterminate" relationship type, not one-to-many

[General Ledger]

I can't get Access 2007 to create a one-to-many relationship.

I have two tables. I drag the field from the one side to the table with the many side. Access opens the Edit Relationships window. The Relationship Type is Indeterminate. There does not seem to be ability to edit the relationship type.

 

[Joe4]

Does this field you are trying to join on always have values in it in both tables (no blanks or nulls)?

Are they both formatted the same (i.e. not trying to join a Text Field to a Numeric field)?

 

[General Ledger]

Q.Does this field you are trying to join on always have values in it in both tables (no blanks or nulls)?

A.Both the From and To fields always have a value. The From table is a complete list of all unique items numbers. The To table are transactions by item number. Some item numbers may not have had a transaction.

Q.Are they both formatted the same (i.e. not trying to join a Text Field to a Numeric field)?

A.Both the From and To fields are formatted as text. The fields are item numbers with leading zeros.

 

[Joe4]

On your "unique" list that you are linking, in the table that it is unique in, is their a Unique Index on that field?

 

[General Ledger]

Joe4,

You got it!!

The set up for the Item Number in the table of Unique Item Numbers was Index Yes Duplicates. I changed to Index No Duplicates. When I went to create the relationship between the tables, Access now acknowledged One to Many.

Thank you so much!

GL