Unanswered: error message when creating relationships
I'm trying to create relationships between my tables in Access with "referential integrity enforced", but whenever I try to select this option (and cascade to update & cascade to delete) the following error message pops up: relationship must be on the same number of fields with the same data types.
Can anyone suggest what's wrong and how I can fix it please.
Seems clear enough to me. You probably try to create a relationship between two columns (fields) that are not of the same data type. Open each table in design view and check the data type definition of the concerned columns.
Hmm...I set the data types for quite a few of the fields at lookup wizard so that it refers to a query....you'll see what I mean if I give you an example....
Table 1 - Ticket Type (TT)
TT_ID (primary key - data type: autonumber)
TT_Type (e.g. standing or seated - data type: text)
This table was then turnd into a query so that TT_ID would actually have the prefix TT before the autonumber - so thats Query 1
Table 2 - Ticket Sales (TS)
TS_ID (primary key - data type: autonumber)
TS_TicketType (data type: lookup wizard, using Query 1)
TS_CommitteeMember (who sold the ticket - data type: lookup wizard)
TS_Donor (who bought the ticket - data type: text - not sure how to create the relationship for this one as it should point to the ID of either an individual donor or company donor, both of which are listed in 2 seperate tables)
I've also attached a colourful picture if that helps to explain it better!
I realise what you meant about the different data types.....
If you notice from the picture I've attached below, basically what I want is the primary key of certain tables to appear in the other tables
I can see why that creates a problem (e.g. if the primary key is set to autonumber in table 1, in the other tables I have used lookup wizard as the data type to get that autonumber) but do you know how I can get around it?
Usually you define the Primary Key column (Identity column) as an Autonumber (provided that you decided to use surrogate keys). If you want to define a Foreign Key column in a related table, you define that column as of Number (Long) data type, not Autonumber and surely not the so-called "multivalue" type.
As fas as the lookup wizard is concerned, stay as far away from it as you can. It causes more problems than it's able to solve.
Be also cautious not to create circular references when defining relationships among table, this would yield an error too.
I tried that with one of the relationships and now I'm able to select the options to enforce referential integrity and cascase to update/delete - so I've got the 1 and infinity symbols.
But, since i've changed the data type from lookup wizard to number (long integer), in database view the drop down list still appears - is this ok? (keeping in mind that you suggsted to stay away from lookup wizard).
With that in mind, is there any other way I can get prefixed codes into another table as a foreign key? What I have at the moment is....e.g. in the ticket sales table, i need a code for the type of ticket being sold, where the different types of tickets are listed in a seperate table and the primary key is set to autonumber, but I've made a query based on this table so that a prefix of TT is added before the autonumber, so the lookup wizard looks up the query of ticket type, not the table of ticket type.....since its using the query to get the prefixed code, if i change the data type from lookup wizard to number (long integer), the relationship will be created normally, but when i delete the lookup options from row source (lookup tab), all i get is a drop down list with no options at all
So basically, how would I get the prefixed codes without using lookup wizard to look up the related query?