Avoid duplicating values within a series of numbers across multiple fields
I have a database with only 1 table in it and currently no relationships to that table. In this table, there are approximately 300 records of customers who will be assigned large groups of admission tickets. Each of these tickets has a unique serial number (1 through 1000000).
Within each record there is a "Starting Number" field and an "Ending Number" field. For example, if the starting number is 1 and the ending number is 1000, there is a calculation field that will output the total quantity (1000). Because the tickets are not always distributed in sequential order, there are multiple "Starting Number" fields and corresponding "Ending Number" fields. For example, Starting Number 1, Ending Number 1, Starting Number 2, Ending Number 2, Starting Number 3... and so on.
My problem is that I currently do not have a way to avoid duplicating a serial number if it is not the same starting number or ending number as the respective field in a different record. I currently have it set up so "Starting Number 1" cannot be duplicated in a different record in the "Starting Number 1" field. Unfortunately, it can be duplicated if "Starting Number 1" is used as "Starting Number 2" in a different record.
Also, if a serial number is in the middle of a series, there is no way to avoid duplicating that serial number in a different series within the same record or on a different record. For example, if Company A receives a group of 1000 tickets from "Starting Number 1" = 1, to "Ending Number 1" = 1000, the system will allow me to enter "Starting Number 2" = 100, even though ticket serial number 100 has already been assigned in the first group of 1000 tickets. Additionally, the system will allow me to enter “Starting Number 1” = 100 for Company B (next record) even though ticket number 100 has already been assigned to Company A.
I need the database to not allow this duplication of serial numbers. Is there a way to make it so that once a serial number has been assigned to one company it cannot be assigned to another company? Do I need a new table that lists every single serial number, then relate the tables with a one-to-many relationship? Or do I need a value list of all serial numbers, and somehow limit each member of the value list to only one occurrence?