By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,247 Members | 1,287 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,247 IT Pros & Developers. It's quick & easy.

Unique with empty records?

P: n/a
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!

Nov 29 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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?

Nov 29 '05 #2

P: n/a
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.
Nov 29 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.