relationship must be on the same number of fields with the same data type

I am brand new to Access and and trying to learn it by slogging my way through the MS Access 2010 Bible.

I am trying to set the relationship between two tables. One is a list of court jurisdictions that has a key field that is an AutoNumber field. A second table is a list of judges. I am trying to link the two in a one to many relationship.

When I link the Jurisdiction field in the Judges table to the Key field in the Jurisdiction field it will not let me enforce referential integrity. It gives me the error message that is the subject of this question. The jurisdiction field in the Judges table is a text field. I thought that might be the problem, but:
  1. it cannot be an AutoNumber field and
  2. I can't change it to a Number type even though I have deleted the relationship. When I try, I get an error message telling me to delete the relationship, but I already have done that.
Thanks for any help.
Answer
Answer
The Jurisdiction column in the Judges table should be a long integer number data type whose value must be one of the values of the autonumber primary key of the Jurisdictions table.  It may be that you have values in the column in the judges table which are not represented in the Jurisdictions table.  You would still be able to create a relationship, but not enforce referential integrity, which would make the relationship pointless of course as it would do nothing to ensure the integrity of the data.  So the first thing to do is check for unmatched Jurisdiction numbers in the Judges

Having a foreign key in Judges does presume that each judge relates to only one jurisdiction, however.  Is that the case or could a judge relate to more than one jurisdiction?  If the latter then you have a many-to-many relationship type between Jurisdictions and Judges.  A many-to-many relationship type is always modelled by a third table, so you'd have tables in outline along these lines:

Jurisdictions
....JurisdictionID (autonumber PK)
....Jurisdiction

Judges
....JudgeID (autonumber PK)
....FirstName
....LastName

And to model the relationship type:

JurisdictionJudges
...JudgeID (long integer number FK)
...JurisdictionID (long integer number FK)

The primary key of the last would be a composite one made up of the two columns.

For data entry you can have a judges form with a subform to enter one or more jurisdictions for each judge, or conversely, a jurisdictions form with a subform to enter one or more judge per jurisdiction. Or indeed both, which gives you the option of entering data either way.   The parent form would be in single form view, the subform in continuous forms view and would include a combo box to select the jurisdiction or judge, set up to show the text value from the referenced table but with the numeric key value as its hidden value.  You'll find an example of this basic form/subform set-up as ParentActivities.zip in my public databases folder at:

https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

You might have to copy the text of the link into your browser's address bar (not the link location).  For some reason it doesn't always seem to work as a hyperlink.

_____________________
Ken Sheridan,
Newport, Shropshire, England

"Don't write it down until you understand it!" - Richard Feynman

5 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Answer
Answer

The Jurisdiction field in the Judges table should be a Number (Long Integer) field. You can then create a relationship with the AutoNumber field in the Jurisdiction table and enforce referential integrity.

The table will display the numeric key value.

On a form based on the Judges table, you can use a combo box with the following properties:

In the Data tab of the Property Sheet:

  • Control Source: the name of the Jurisdiction field in the Judges table.
  • Row Source Type: Table/Query.
  • Row Source: the name of the Jurisdictions table.

In the Format tab:

  • Column Count:2
  • Column Widths: 0";1"

The combo box will store the numeric key value, but display the second field from the Jurisdiction table.

---
Best wishes, HansV
https://www.eileenslounge.com

9 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated May 9, 2024 Views 32,686 Applies to: