A simplified Access database of my Employment Services application is attached below.
The database is intended to record details in order to create Invoices for Placements and Retainers.
These can be related, as a Retainer fee may be charged first, and then followed by a Placement fee when the position has been filled.
As a result, the Placements and Retainers tables can
be linked via an AssignNumber in the Assign table (ie. short for Assignments)
The database is also intended to provide details of potential future Pipeline work.
This Pipeline table essentially records a description of future Employment Services potentially required by a client.
It is also linked to the Assign table.
The relationships are:
The Problem: How To Provide A Number for Pipeline Submissions
As it is currently designed, the database is auto-creating an Invoice number, even for Pipeline work.
This invoice number is calculated in the format YYMMDD + a 2-digit number based on number of invoices that day.
Eg. the second invoice on 25-Jan-2018 will appear as:
Obviously potential pipeline work should not
receive an Invoice number, because it won't be sent out as an Invoice.
If I continue to use the Invoice Number for Pipeline submissions, then the Invoices that do get sent out to clients potentially won't have sequential numbers.
However, I'm at a loss as to how to handle this.
For example, I've wondered if a Pipeline submission should have an "Invoice Number" of, say, "Pipe-00001" (for the first Pipeline submission)
But this route would require some fancy calculation to find and increment the last Pipe-0000x".
So I'm wondering what would be a better practice, or best practice, for this problem.
Here is what the Invoice Number looks like for Placements;
And here is what the Invoice Number looks like for Pipeline:
Obviously the Invoice Number looks wierd for Pipeline work, since no Invoice would be sent out.
But I'm somewhat stumped as to how to handle this.
I'm wondering if this PipelineNumber should also appear in the InvoiceNumber column?
Or should there be two columns: one for InvoiceNumbers, and one for PipelineNumbers?
Or should there be a separate table?
All these ideas seem far from elegant, and I'd prefer to avoid some fancy calculation method.
Any suggestions/solutions for this please?
Many thanks in advance,