AutoIncrement 2nd part of a number, based on the first.
I'm trying to build a Terminal Management System for a client, who has a specific need.
They have Contracts and then Loads against those Contracts. For example, Contract # = 10001 might require 3 loads to complete, so the 3 loads would be 10001-001, 10001-002, & 10001-003. And Contract 10002 might require, 10002-001, 002, 003, & 004.
The previous software package, which I have been asked to replace, performs on the following logic:
- User selects contract #.
- The system looks for the next available load # for that specific contract and returns that for the load number
Example: User selects Contract # = 10001. The system looks and finds 10001-001 and 10001-002 currently exists, so it returns the next available load 10001-003.
Example: User selects Contract # = 10002. The system looks and finds that no loads have been assigned to 10002 and as such, returns 10002-001.
I cannot for the life of me, figure out how to get a calculation that will perform this function.
I have 2 tables. Contract (ID, Contract#) and Load (ID, Contract #).
The reason I have to stick to this format, is because the client intends to have different vendors setup their own loads, and requires the system to just give them the next number, rather than allow them to select their own number.
Your help and advice would be greatly appreciated.