New Table Errors

[css1270]

I am creating a table for Change orders. In my new table I have Survey No (Job No) and the respective Change orders. Usually all change orders range from 001 to 005. Instead of retyping my Survey No I wanted to have it look it up. When I do so, the lookup field is blank. I tried to create a relationship and when I do I get "No unique index found for referenced field of the primary table." What the heck is going on?? I don't understand why I cant lookup the data in another field. Very strange to me. Please help. The database is attached.

[dblife]

if you are trying to make a field the primary key or enforce referential integrity after you have put values in the table, access will give you this error.
You must have duplicate values in the field you are trying to relate.

[css1270]

I tried to relate the field before and after data was entered......., I get the same result.

Did you look at the database?

The data "HAS TO BE DUPLICATE" its a lookup. (What ever is in the primary table will go in the other) I don't have control over how it is entered into the new table if I am using lookup. Therefore they are duplicate.

[dblife]

i could not open the file you included.
are you using lookups at table level?
ms access allow lookups at table level but they are best avoided as they wreak havoc when writing queries etc later on.
if you are using lookups at table level, try removing them?

[css1270]

Yes I have lookups at table level. My data base is large and in the beginning when I was creating tables and doing analysis, MS Access created tables using the lookups for repeated values, just as I have now. I have also created many queries using data that are in the lookups and I have yet to encounter problems. There must be a better solution than to recreate my entire database that is working fine until now. The ONLY problem with my database is this very simple table that isn't working right.

[orange]

Do not do lookups at the table level.
see http://www.mvps.org/access/lookupfields.htm

[css1270]

That would require me to redo my entire database. That IS NOT going to happen for two reasons. The lookups work just fine unlike the sediments expressed earlier. And two that would require days of redoing the database. I need other advice that addresses my problem. Thank You.

[orange]

Can you post an mdb version of your database - no confidential info, just enough data top show the problem? I don't have 2007 and can not open an accdb.

[css1270]

Sorry, I cant save it down because my database uses 2007 features.

[orange]

Then I suggest you post a 2007 version without any private info, and ask specifically for people to try
x--
y---
where these are specific issues.

It looks like you have zipped a file that you had already compressed with rar. That seems to be the issue dblife had.

You could just zip the accdb.
Good luck.

[css1270]

I am re-posting my original thread. I have a database that is over two years old, all works well with queries and reports. Today I created a table for Change orders. In my new table I have Survey No (Job No) and the respective Change orders. Usually all change orders range from 001 to 005. Instead of retyping my Survey No I wanted to have it look it up. When I do so, the lookup field is blank. I tried to create a relationship and when I do I get "No unique index found for referenced field of the primary table." What the heck is going on?? I don't understand why I cant lookup the data in another field. Very strange to me. Please help. The database is zipped and attached.

[css1270]

In form view when I pull down "Survey No" the lookup it's empty. Why would it be empty? In my previous post is the stripped down database with these objects in it. Attached herein is a copy of the query within the table and then a copy of the run query. Please help.