Unique with empty records?

[ET]

Hi!

I'll need help on what todo with this information in table...

There has to be a unique on one column (Item ID, for inventory purpose)
but in many cases that ID is not known... thus, many empty records for
that column exist in a table.
I tried to create unique index (no duplicates), of course, it failed
because of many empty records...
Now, is there ANY way to keep those empty records, and allow new empty
records for that field,
but if user tries to enter ID that already exist in a table, the Access
will give warning message about
violation of unique?
Thank you!

[Allen Browne]

1. Open your table in design view.

2. Open the Indexes dialog (View menu.)

3. Select the index on the [Item ID] field.

4. In the lower pane of the dialog, leave Unique as Yes, and change Ignore
Nulls to Yes.

The index now permits any number of null values, but no duplicates.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ET" <ve****@yahoo.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

I'll need help on what todo with this information in table...

There has to be a unique on one column (Item ID, for inventory purpose)
but in many cases that ID is not known... thus, many empty records for
that column exist in a table.
I tried to create unique index (no duplicates), of course, it failed
because of many empty records...
Now, is there ANY way to keep those empty records, and allow new empty
records for that field,
but if user tries to enter ID that already exist in a table, the Access
will give warning message about violation of unique?

[Trevor Best]

ET wrote:

Hi!

I'll need help on what todo with this information in table...

There has to be a unique on one column (Item ID, for inventory purpose)
but in many cases that ID is not known... thus, many empty records for
that column exist in a table.
I tried to create unique index (no duplicates), of course, it failed
because of many empty records...
Now, is there ANY way to keep those empty records, and allow new empty
records for that field,
but if user tries to enter ID that already exist in a table, the Access
will give warning message about
violation of unique?

One of the properties of an index is "Ignore Nulls", this should work
for you.