asked on

MS Access Unique Index on Fields Help Please

I am needing help with Unique indexing some fields in a table called "Orders".

"BobsOrderID"
" JimsOrderID"

"Orders.BobsOrderID"
"Orders.JimsOrderID"

Sometimes "BobsOrderID" may be filled out and sometimes "JimsOrderID" may be filled out. Sometimes maybe both will be Null. Depends on where the order comes from
I need help determining on how to set these so the order IDs are unique to each field.
BobsOrderID = 12345,12345 BAD
BobsOrderID = 12345,54321 GOOD
JimssOrderID = 12345,12345 BAD
JimsOrderID = 12345,54321 GOOD

But
BobsOrderID = 12345 AND
jimsOrderID = 12345 GOOD

The Nulls and combinations are throwing me off. I hope this makes sense and thank you for the help.

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dustin Stanley

ASKER

I thank you for the answer. I tried to delete this question but EE would not allow me to delete it while it was in pending. I figured it all out and it was an issue with me uploading "" and "" is a value which was causing the errors. "" and Null are different. Thanks.

mbizup

Hi -

Just for future reference, you CAN accept your own answer as a solution...
Just the same, I'd recommend the structure I suggested above if you are not too deeply into this design. It would give you more flexibility for future growth.

Dustin Stanley

ASKER

I thank you and that is how it is set up. In my scenario Bob and jim were markets for orders we receive NOT customers. We download a CSV file with multiple market orders daily and it is setup to have all the markets in one single file. So then I have to seperate them. I appreciate the help.