• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 876
  • Last Modified:

Getting error "Invalid field definition 'Customer Service ID' in definition of index or relationship

I keep getting the above error when trying to create a relationship between fields called "Claim ID" and "Customer Service ID".  Both fields are Autonumber primary key fields, and I have Enforce Referential Integrity checked when trying to create the relationship.

I tried checking Access help, and all I get is:

Possible causes:

·     The field name you've specified might be misspelled. Check the spelling of the field name.

·     The field type you're using is a Memo or OLE Object, which cannot be indexed.

I'm pretty sure everything is spelled correctly, and the field type is Autonumber, not Memo or OLE object, so I don't think that is the problem.

Anybody have any suggestions?
  • 3
1 Solution
You cannot create a relationship between two AutoNumber fields!
If you need further help then you will have to tell me what type of relationship you are trying to create.  i.e. How are claims and customer service connected?
jasons78Author Commented:
I'm creating a db for file audits.  I originally had everything in one table, but since there are 5 general areas that a file is audited on, it seemed more practical to break up that table in to the 5 different areas.  I now have 6 tables named tblClaimInfo, tblCustomerService, tblInvestigation, tblDocumentation, tblPayment, and tblRecovery.  TblClaimInfo holds the general info about the claim; claim number, branch office the file is in, adjuster's name, etc.  The other 5 tables hold the criteria the file is audited on.  

So basically all the information needs to be linked back to the claim number, but I don't want to use the claim number as the primary key, just in case sometime in the future the same file gets audited twice.
Simple enough.  It sounds like you need a lot of one-to-one relationships.  But before I get into that, you need to take a good look at the overall structure.  If you ever have multiple audits of a file, then you are going to need some way to group those audits together.  In other words, you need to take fields from TblClaimInfo that you think will be the same for every audit (I assume some information about the file itself) and make a separate table just for that.

Then, even though you have a bunch of parity relationships due to the need to separate a big table, you will have a least 1 many-to-one relationship between the file and each claim number.  I feel as though I am confusing 'claim' and 'audit' here, so I hope you understand.

Now, to create a one-to-one relationship you will need one table with an AutoNumber primary key, and another table with a long integer field to relate to it.  By far the easiest way to set this up is to create the primary key for one table, and then in the related table when you set the data type, pick "Lookup Wizard".  Tell it to include the primary key and at least one other useful field and then it will automatically create the relationship for you :)

One thing to note about this is that Access does not set "Enforce Referrential Integrity" by default if it makes the relationship for you.  So after you are done editing the tables, go into the Relationships window and explore the properties there.

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now