Error: No unique index found for the referenced field of the primary table

[Dean93]

Hi all,



in order to set up a query i want to build a relationship between a couple of tables. Unfortunately there was no primary key given when i started working with the database.

Assume we have Tables

A
B
C
D

they all have a similar structure with columns

Index (primary key)
Date
Name or ID (which is not "unique" as every ID has an entry for different dates, but every individual has a unique ID)
and every table has different properties listed in the other columns (lets assume it is the weight, the hair colour etc).


What i want now? I want a query which gives me Date, ID, Name and properties from different tables.
I just set up an Index because i thought that we need a unique primary key. But unfortunately i am stuck building up a relationship between these tables. I guess i want to refer the column "ID" to "ID". But then the error message appears.

Can anyone help or do you need more information?

Thanks+

[John_G]

The first thing you need to do is rename the fields from "Index" to something else. "Index" is a reserved word, and I think Access is getting confused about what you are doing. The same goes for "Date" - it should not be used as a field name either.

Which field in your tables is common to all of them, in other words, how do you know which records in Table B refer to the same person (?) as a record in Table A?

It would help a lot if you gave us some real table names, and real data to work with.

[Dean93]

The first thing you need to do is rename the fields from "Index" to something else. "Index" is a reserved word, and I think Access is getting confused about what you are doing. The same goes for "Date" - it should not be used as a field name either.

Which field in your tables is common to all of them, in other words, how do you know which records in Table B refer to the same person (?) as a record in Table A?

It would help a lot if you gave us some real table names, and real data to work with.

Thanks for your reply.

The combination of "Date" and "ID" tells me which records belong together. For example:

I have an entry for the years 1999 and 2000 for every person, which means we have two entries belonging to one person. As a consequence I can figure out which entries belong together if i match up these records, which have the same "ID" and (!) the same "Date".

I will rename the field Index. "Date" was just a placeholder. But thanks anyway!

[John_G]

OK - but you have 4 tables (A, B, C, D). How does Table B differ from Table A, for example? Are there multiple entries for each person in each table?

You should be able to join the tables in a query using the two fields ID + Date, provided the combination of Date + ID is unique within each table.

[Dean93]

OK - but you have 4 tables (A, B, C, D). How does Table B differ from Table A, for example? Are there multiple entries for each person in each table?

You should be able to join the tables in a query using the two fields ID + Date, provided the combination of Date + ID is unique within each table.

They differ e.g. in the properties they give us for different poeple at different dates.

Table A has the columns Date, ID, "Index" (AutoNumber, primary key), height
Table B has the columns Date, ID, "Index" (AutoNumber, primary key), weight

It could be that an individual has no entry in A but in B, vice versa.

Now i want to join the tables. Therefore i try to setup a relationship. But then the error messages appears ("No unique index found.."). Do I have to "enforce referential inegtrity"?

[John_G]

It could be that an individual has no entry in A but in B, vice versa.

Now you have a problem. There is no type of relationship that works both ways. You can have one that shows "In B but not in A" OR one that shows "In A but not in B", but not both.

I think you need to re-think your data structure - your data can easily all be in one table:

Date
ID
Height
Weight
... etc.

You could include an autonumber "Index" if you wanted to, but if you're not going to use it in relationships, there's not need for it at the moment.

If you can tell us a bit more about what you are trying to accomplish, that will help.

[Dean93]

Now you have a problem. There is no type of relationship that works both ways. You can have one that shows "In B but not in A" OR one that shows "In A but not in B", but not both.

I think you need to re-think your data structure - your data can easily all be in one table:

Date
ID
Height
Weight
... etc.

You could include an autonumber "Index" if you wanted to, but if you're not going to use it in relationships, there's not need for it at the moment.

If you can tell us a bit more about what you are trying to accomplish, that will help.

But if i dont include an autonumber "Index", what am i supposed to use as an primary key?



I actually want to build up a table with all the data i need. Like you said

Date
ID
Height
Weight
... etc

where the entries are emptry, where i have none. I thought I need to set up a relationship between the table, then set up a query and filter for the columns i need. Is there a better way to do this?

[John_G]

But if i dont include an autonumber "Index", what am i supposed to use as an primary key?

You have Date + ID as a compound primary key (uses more than 1 field)

Here's what I would do to merge the data from your 4 tables into 1.

First, create your new table as you and I both described. Set the primary key to be ID + Date, but leave the table empty.

Now, create an Append query, with Table A as its source, and appending records to the new table you just created. Only include fields ID and Date from Table A in the new records. When you run the query, the new table will have the same number of records in it as Table A does.

Now modify the Append query to use Table B as its source, but still appending records with the same two fields to the new table. This time when you run the query, you will most likely get an error message that says some records cannot be appended due to key violations (or something like that). There is nothing wrong - it is doing exactly what you want it to do, i.e. prevent any duplicate (ID + Date) records in the new table. Select the option in the error message that allows you to execute the query anyway.

Do the same for tables C and D.

What you have now is you new table with all possible combinations of ID + Date from tables A, B, C and D.

Now, for step 2 you will create an Update Query. Add to the query the new table, and Table A. Join these two tables on ID and Date. Include in the field grid the field from the new table that matches the measurement field in Table A. For example, if Table A has fields ID, Date and Height, include Height from the new table in the field grid. In the "Update to" row for that field, put [Table A].[Height].

When you run the query, the Height values will be copied from Table A into the corresponding field in the new table. You may or may not get a message asking you to confirm the update, depending on your MS Access settings.

Now modify the Update query to use Table B, and updating the appropriate measurement field in the new table to [Table B].[Weight] (for example)

Do the same for Tables C and D and you're done.

I don't think you will get an error message like you described in the your initial post, but if you do, modify the offending table by deleting the Autonumber PK, and creating a new PK with ID + Date. That should fix it.

Good luck, and let us know how it goes.

[Dean93]

You have Date + ID as a compound primary key (uses more than 1 field)

Here's what I would do to merge the data from your 4 tables into 1.

First, create your new table as you and I both described. Set the primary key to be ID + Date, but leave the table empty.

Now, create an Append query, with Table A as its source, and appending records to the new table you just created. Only include fields ID and Date from Table A in the new records. When you run the query, the new table will have the same number of records in it as Table A does.

Now modify the Append query to use Table B as its source, but still appending records with the same two fields to the new table. This time when you run the query, you will most likely get an error message that says some records cannot be appended due to key violations (or something like that). There is nothing wrong - it is doing exactly what you want it to do, i.e. prevent any duplicate (ID + Date) records in the new table. Select the option in the error message that allows you to execute the query anyway.

Do the same for tables C and D.

What you have now is you new table with all possible combinations of ID + Date from tables A, B, C and D.

Now, for step 2 you will create an Update Query. Add to the query the new table, and Table A. Join these two tables on ID and Date. Include in the field grid the field from the new table that matches the measurement field in Table A. For example, if Table A has fields ID, Date and Height, include Height from the new table in the field grid. In the "Update to" row for that field, put [Table A].[Height].

When you run the query, the Height values will be copied from Table A into the corresponding field in the new table. You may or may not get a message asking you to confirm the update, depending on your MS Access settings.

Now modify the Update query to use Table B, and updating the appropriate measurement field in the new table to [Table B].[Weight] (for example)

Do the same for Tables C and D and you're done.

I don't think you will get an error message like you described in the your initial post, but if you do, modify the offending table by deleting the Autonumber PK, and creating a new PK with ID + Date. That should fix it.

Good luck, and let us know how it goes.

Maybe I am just not the best with Access (yet) but I fail hard with that query. When i try to create the table containing all the data with an append query it says that i cant add data due to key violations. I put "NAME" and "ID" as composed primary keys in 2 tables and then in the empty table.

Well, I just dont know how to solve this.

I guess I have a couple of problems. One of them is: The different tables have columns which are named the same but contain different data (is that a problem)? But: I dont need same named columns, which is basically good as i could delete them.

The different tables can contain data for different individuals. e.g. person B is listed in table A, but not in table B.

Can it be, that this is a cause for my problem?

Here is a "prototype" database, which only contains two of the tables and even less columns / data. NewZip.zip