Key and Index issues

I have a table without any key defined for it.
But I have an index comprising of 4 fields say -
1. Field1 Not Null
2. Field2 Can be Null
3. Field3 Can be Null
4. Field4 Can be Null
I have data in Field1 and Field2. However Fields Field3 and
Field4 are both Null.
Field1 = 'A' and Field2 = 'BB01' contain the same data
for two records.
I found this problem -
1. In the database I see two records
created for the above. The only difference being
the description in two records is different.
2. When I goto retrieve records using Access database,
it shows me 1st record twice.
'cause it shows the same description for both records.
How can I ensure that -
1. It won't create two records with the same key.
2. It should retrieve records correctly.
Thank you,
-M
"Me" <Me@.discussions.microsoft.com> wrote in message
news:B89EA964-D9B9-4AE4-97D0-F1A9B0232C74@.microsoft.com...
>I have a table without any key defined for it.
> But I have an index comprising of 4 fields say -
> 1. Field1 Not Null
> 2. Field2 Can be Null
> 3. Field3 Can be Null
> 4. Field4 Can be Null
> I have data in Field1 and Field2. However Fields Field3 and
> Field4 are both Null.
> Field1 = 'A' and Field2 = 'BB01' contain the same data
> for two records.
> I found this problem -
> 1. In the database I see two records
> created for the above. The only difference being
> the description in two records is different.
> 2. When I goto retrieve records using Access database,
> it shows me 1st record twice.
> 'cause it shows the same description for both records.
> How can I ensure that -
> 1. It won't create two records with the same key.
> 2. It should retrieve records correctly.
> Thank you,
> -M
>
>
Can you post the DDL to CREATE the table in question, some INSERT statements
to populate and then show us the results that you would like to get back? I
would have to make several assumptions here about your data and how it is
being used.
Rick Sawtell
MCT, MCSD, MCDBA
|||What is typical when you link or import a table in MS Access is that it will
ask for a key for the data, if you answer this question and provide some
columns, Access will give you the behavior you describe.
Drop the linked table and remap it, except, this time, when it asks for a
key, cancel the dialog. This will force Access to assume all columns for a
key and should provide you with the duplicate information.
Sincerely,
Anthony Thomas

"Me" <Me@.discussions.microsoft.com> wrote in message
news:B89EA964-D9B9-4AE4-97D0-F1A9B0232C74@.microsoft.com...
> I have a table without any key defined for it.
> But I have an index comprising of 4 fields say -
> 1. Field1 Not Null
> 2. Field2 Can be Null
> 3. Field3 Can be Null
> 4. Field4 Can be Null
> I have data in Field1 and Field2. However Fields Field3 and
> Field4 are both Null.
> Field1 = 'A' and Field2 = 'BB01' contain the same data
> for two records.
> I found this problem -
> 1. In the database I see two records
> created for the above. The only difference being
> the description in two records is different.
> 2. When I goto retrieve records using Access database,
> it shows me 1st record twice.
> 'cause it shows the same description for both records.
> How can I ensure that -
> 1. It won't create two records with the same key.
> 2. It should retrieve records correctly.
> Thank you,
> -M
>
>
|||If you don't specify any columns for the unique identifier
and there is no primary key, Access will assume there are no
columns that uniquely identify a record (rather than all
columns). This is why you end up with a read only recordset
when linking tables under such a scenario.
-Sue
On Sun, 14 Nov 2004 23:11:58 -0600, "AnthonyThomas"
<Anthony.Thomas@.CommerceBank.com> wrote:

>What is typical when you link or import a table in MS Access is that it will
>ask for a key for the data, if you answer this question and provide some
>columns, Access will give you the behavior you describe.
>Drop the linked table and remap it, except, this time, when it asks for a
>key, cancel the dialog. This will force Access to assume all columns for a
>key and should provide you with the duplicate information.
>Sincerely,
>
>Anthony Thomas