Can't define relationships for new tables

[Robway]

I've just added two new tables to my existing 14-table database, and can't define the new relationships how I'd like.

The 2 new tables are tbContainerType and tbContainer.

I want them to link as:

tbContainerType 1-many tbContainer 1-many tbAccessionItem

I have a field ContainerType in both tbContainerType (primary key/auto number) and tbContainer (text) and a field ContainerID in both tbContainer (text)and tbaccessionItem (text).

I want referential integrity between them with update and delete cascades but I haven't been able to set this - the fields are greyed out.

The ContainerID field has always existed in tbAccessionItem and I've gone through it to check that every record is accurate. I've created all the records for tbContainerType (there are only about 10) and for tbContainer (1359 records and growing) so that the itegrity etc is there (I hope - I keep checking), but it still won't work.

Any suggestions on what I've missed?

Cheers, Robyn

 

[Robway]

Just realised I have been trying to create a relationship between an auto number field and a text field. I've changed the text one to a number field (long intiger) and made sure all the data in it complies, but still no joy. I get the error message "Relationship must be on the same number of fields with the same data types."

Help.....

 

[BattleDroid42]

The joined fields must be of the same datatype as you've already discovered. It sounds like one of your tables has a composite primary key (i.e. a key made up of more than one field)? If this is the case you either need to change the tables to use a single field primary key in both tables or composite keys in both tables.

 

[Robway]

Thanks for replying.

I haven't got any composite keys - although it's something I'm seriously thinking of introducing to one of my tables, but that's another problem altogether. I'm going to keep checking that all the data in the various linked fields is really compliant, as I suspect that may still be where I have a problem.

I've created new tables and new relationships often in the past, but never run into this before.

Cheers, Robyn