Invalid ID field name

[John Sh]

I have three tables to be set up as a relational DB. They are to be set up as one to one and it works fine until I check the integrity and cascade boxes. I then get a message " Invalid field definition 'ID' in definition of index or relationship". No matter what field name I enter I get the same message. I have tried changing the key to / from primary to no avail. I understand the role of primary and foreign keys but am flummoxed by this error. Any help / suggestions gratefully received.
John

 

[Jon]

Welcome to the forums! We are the most active Microsoft Access community on the internet by far, with posts going back over 20 years!

Here are just a couple of tips for you:

1. Feel free to ask any question you like, however basic you may feel it is, or even if it has been answered before. Our expert members thrive on helping you out!

2. If you prefer a dark theme to the forums, just go to the bottom left of this forum and click "Default style". You will then see a selection of themes to choose from. I like Shades of Blue.

3. If you like any of the answers you get, feel free to click the "Like" link on the bottom right hand corner of the post. If you hover over the Like link, you can even choose the type of smiley.

Above all, hang around here, have fun, learn stuff and join in.

 

[MajP]

No idea why you would need a "One to One" relationship. Pretty rare to do that. Can you explain what fields you are joining on? What are the primary and foreign keys? And what indices have you applied to the table? My guess is you are trying to establish relationship on a PK that is not unique and thus not indexed. Or as you said you have a cascade delete linked to a primary key, which I also doubt you can do. You could also just show a screen shot.

 

[The_Doc_Man]

It is almost impossible to have a one-to-one-to-one relationship. (You said you had THREE tables with one-to-one...) and enforce relational integrity. Among other things ONE of those tables will physically have to be updated first, but the integrity constraints from the other two tables will always stop you.

As a side note I might have to exercise my moderator authority to move this to a "database design" heading, since the "Intro" forum is really not meant to use for problems. Oh, don't worry about violating rules... we are pretty loose here when we need to be.

 

[John Sh]

It is almost impossible to have a one-to-one-to-one relationship. (You said you had THREE tables with one-to-one...) and enforce relational integrity. Among other things ONE of those tables will physically have to be updated first, but the integrity constraints from the other two tables will always stop you.

As a side note I might have to exercise my moderator authority to move this to a "database design" heading, since the "Intro" forum is really not meant to use for problems. Oh, don't worry about violating rules... we are pretty loose here when we need to be.

Sorry about that. I realised my mistake as soon as I hit the Post button. I will try to be more careful in the future.
John

 

[John Sh]

No idea why you would need a "One to One" relationship. Pretty rare to do that. Can you explain what fields you are joining on? What are the primary and foreign keys? And what indices have you applied to the table? My guess is you are trying to establish relationship on a PK that is not unique and thus not indexed. Or as you said you have a cascade delete linked to a primary key, which I also doubt you can do. You could also just show a screen shot.

First let me thank both respondents for your replies. Obviously what I am doing is a bit different, so;

A bit of history to get things straight.
I have just completed the job of photographing some 12,000 specimens for a local herbarium. In the process I noticed the database was in complete disarray so decided to clean it up. The database has 12,000+ records and about 90 fields, most of which are unique to a particular specimen so the opportunity for smaller, related, tables is limited. Each record is defined by a unique "Accession Number" that is manually generated and may, or may not, have a decimal point, hence the one to one. That was Access's doing, by the way, not mine. I have split the main table into three sections, and related them, one to many, by converting the ID key to Replication ID. I have now created three linked forms to facilitate data entry, the first of which will have drop-downs for data consistency with some fields automatically filled with date etc. The data in the other forms is specimen specific so must be entered individually
My next little problem is how to reference data from table "A" into form "B". I need a new accession number to be transferred from a new record in table "A" to the appropriate fields in tables "B" and "C".

My journey into Access is just beginning. My knowledge of VBA is nil although I have some recent experience with java and Clipper / DB3 many years ago so am familiar with the basic concepts of programming.

A bit long winded but hopefully clears things up.
John

Old I may be but this dog thrives on new tricks.

 

[MajP]

I am still confused on the one to one. IF and ONLY IF, you split the table into three tables because some records have table 2 type data but not all, and some records have table three type data but not all and thus you are trying to conserve space. On paper that sounds logical in reality with only 12 k records you are creating more pain then benefit.

IF that is what you did. Then you simply make AccessionNumber the PK in all three tables and it is also the foreign key in 2 and 3.
Now you build your form off of a query (left outer join) from table 1 to table 2. When you enter information on the table 2 size you will automatically create the foreign key in table 2/3.

 

[MajP]

See demo query. This is a real 1 to 1. In the query if I enter data on the table 2 part of the query, Magic happens. A link is automatically created without a subform.

 

[John Sh]

Hi MajP.
My description made sense to me, but not clear enough.
Given the original table has 90 fields, fields 1 to 20 form the new table 1. 21 to 50 table 2 and the rest in table 3. So the original table has been split sideways, not by record type. Each of the new tables have 12,000 records. BTW the one to one is not important, I'm happy with one to many.
My criteria for splitting was not to conserve space but to make all of the data more accessible on a single screen via the linked forms. I have played with the sample, thank you, but can't make head nor tail of it. I think I should make a study of the query language / methods and VBA before I proceed further.
I thank you for your efforts so far. Rest assured I will be back but hopefully a bit better prepared.
John

 

[MajP]

My description made sense to me, but not clear enough.
Given the original table has 90 fields, fields 1 to 20 form the new table 1. 21 to 50 table 2 and the rest in table 3. So the original table has been split sideways, not by record type. Each of the new tables have 12,000 records. BTW the one to one is not important, I'm happy with one to many.
My criteria for splitting was not to conserve space but to make all of the data more accessible on a single screen via the linked forms.

If that was you plan that was a very bad plan and unneeded. However, if you look at my example it does exactly what you did. Table 1 has half of the fields, Table 2 has the rest. They are linked 1 to 1. Making this 1 to many now makes even less sense. Actually no sense.

You do not split tables for display purpose. You simply may query 1, 2, 3. You could put all of this on three tabs of a tab control. No code needed anywhere.

On the main form you could have top level data
Specimen Name Specimen Location

Tab 1 (Specimen Taxonomy Information)
Tab 2 ( Specimen Description)
Tab 3 (Specimen Photos)

If it was me. I would now link the three tables together and run a make table query to combine them back. There is absolutely no need for three tables and you are causing pain where it is not needed.

You only need to break these into separate queries if I want to be in continuous form view and show many records at one time. If you want to work on one record at a time then you can just use a tab control and put different fields on each tab as shown above.

 

[The_Doc_Man]

There are a few reasonable cases where 1/1 relationships make sense. If among your 90 fields, you have more than 4000 characters in a single record, then what you did wasn't wrong. But to avoid referential integrity issues, only one of those tables can actually a "one" side. The other two tables should be one/many BUT with an additional separate constraint that the Accession Number is unique in each table.

The reason for the one/many relationship is that oddly enough, 0 is counted the same as "many" for the purposes of this relationship. SO when you start to add data, if you add to the "main" table first and then add data to the supplemental tables second, Relational Integrity won't eat your socks.

Note that if you could compress those fields so that you could fit all 90 fields into a single record that would not blow out the size constraints of Access itself, you could write QUERIES that did the field splits for you.

 

[gemma-the-husky]

I think a replcationID is intended to enable distributed systems to be subsequently merged with minimal chance of a clash of ID number. I think a replicationID is a GUID rather than number. I suspect it's not required in your situation.

 

[RogerCooper]

A bit of history to get things straight.
I have just completed the job of photographing some 12,000 specimens for a local herbarium. In the process I noticed the database was in complete disarray so decided to clean it up. The database has 12,000+ records and about 90 fields, most of which are unique to a particular specimen so the opportunity for smaller, related, tables is limited. Each record is defined by a unique "Accession Number" that is manually generated and may, or may not, have a decimal point, hence the one to one. That was Access's doing, by the way, not mine. I have split the main table into three sections, and related them, one to many, by converting the ID key to Replication ID. I have now created three linked forms to facilitate data entry, the first of which will have drop-downs for data consistency with some fields automatically filled with date etc. The data in the other forms is specimen specific so must be entered individually
My next little problem is how to reference data from table "A" into form "B". I need a new accession number to be transferred from a new record in table "A" to the appropriate fields in tables "B" and "C".

If you have 90 fields most of which are unique to single record, then they should not be fields at all. You should have a "Comments" field with a "Long Text" type where you can enter free-form information.

 

[MajP]

If you have 90 fields most of which are unique to single record, then they should not be fields at all. You should have a "Comments" field with a "Long Text" type where you can enter free-form information.

NO!!! What are you saying. You most absolutely do not do that.

 

[Pat Hartman]

I think if you break down the specimens into their scientific classifications, you will have a better understanding of what those 90 attributes are and to which type of specimen they apply. 12,000 specimens is a lot but no where near the number of individual types especially if there are duplicates for any given type.

Here is a link to one type of chart -- What goes on a plant kingdom chart? – Big Picture Science (myshopify.com)

Here's a picture of one that is slightly different. At some point you can say, these 20 attributes are common among all the specimens and the others fall into four different branches. That gives you a rational way to break up the columns and it will also make defining your forms simpler. It really depends on the focus of the collection. Maybe they are all Seed-Producing Plants. If so, that narrows it down to that path of the hierarchy. You can expand the application if you add additional paths. This is not my area of expertise so I can't tell you how you should organize the data but these two suggestions should get you thinking about logical groupings.