Senin, 25 Januari 2016

Re: [MS_AccessPros] Primary Key


You are referring to 'Ignore Nulls'?. I tried both Yes and No. It does not make a difference. It still accepts the same Color multiple times without a Season. It only forces me to enter a (combined) unique value if the Season is filled in.


---In, <wrmosca@...> wrote :

Sarah - Pardon my jumping in...
No, Zero-length strings are not the same as nulls. When you use the Index wizard box you will see a property line for allowing nulls. That's what Crystal was referring to.


---In, <sarahk@...> wrote :

I am following your instructions and making a unique index on the combination. 
By 'allow nulls' do you mean 'Allow Zero Length"=Yes?

---In, <strive4peace2008@...> wrote :

Hi Sarah,

don't use that as a primary key -- make the primary key an autonumber.

Instead, create a unique index on the combination and allow nulls ~ I gave you the steps to do that :)

warm regards,

~ have an awesome day ~

On 1/25/2016 12:21 PM, sarahk@... [MS_Access_Professionals] wrote:

Hi Crystal,
I tried this but I am not getting the desired results.
The fields are Color and Season. The combination has to be unique. The Season is optional.
I defined the Index as Color and Season. Primary = No ( If I try yes, then I get message 'Index or primary key can not contain a null value'.)
I can not enter the same Color and the same Season in the table, but it allows me to enter the same Color without a Season multiple times in the table.
What am I doing wrong.
---In, <strive4peace2008@...> wrote :

Hi Sarah,

by definition, a Primary Key is one or more fields.

I usually use an AutoNumber as the Primary Key, and then a Long Integer as the corresponding Foreign Key.

You can define a UNIQUE INDEX on a field or combination of fields too.

Make a Unique Index on multiple fields:

1. go to the Design view of the table
2. make a unique index on the first field
-- click on the field
-- set Indexed in bottom pane to Yes (No Duplicates)
3. open the indexes window (lightening bolt icon on the Design ribbon)
4. in the second column, the field column, add a second field to the same index by filling out the next row. The first column of the next row will be blank
5. if necessary, add a third field the same way

If you want some of the fields in the index to be allowed to not be filled out then set Ignore Nulls to Yes (in addition to Unique is Yes) in the properties in the Indexes window

warm regards,

~ have an awesome day ~

On 1/25/2016 10:04 AM, sarahk@... [MS_Access_Professionals] wrote:

Is it possible when  defining multiple primary keys for a table, to specify that only the combined primary keys can not have duplicates , but the individual key can have duplicate entries?



Posted by:
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (18)



Tidak ada komentar:

Posting Komentar