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.
LVL 1
Dustin StanleyEntrepreneurAsked:
Who is Participating?
 
mbizupCommented:
Seems like your tables could use some restructuring.

I'd suggest a Customer table with names, addresses etc and a CustomerID.

Then the orders table would have order information, including the CustomerID.  So something like this:

tblOrders
------------
OrderID - autonumber, primary key
OrderNumber:  Text (?)  12345, 01234 etc
CustomerID -  Foreign key linking Orders table to customers table where CustomerID indicates Bob, Jim (or Sally, Joe, Billy-Bob, etc)
0
 
Dustin StanleyEntrepreneurAuthor Commented:
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.
1
 
mbizupCommented:
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.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
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.
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.