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

Posted on 2003-02-22
Medium Priority
Last Modified: 2007-12-19
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?
Question by:jasons78
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
LVL 11

Expert Comment

ID: 8002094
You cannot create a relationship between two AutoNumber fields!
LVL 11

Expert Comment

ID: 8002108
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?

Author Comment

ID: 8003772
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.
LVL 11

Accepted Solution

miqrogroove earned 200 total points
ID: 8003831
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question