Calculating a unique incident number for customers incidents
Greetings all. I'm a brand new user with a few hours of training time so I'm looking for some help from more experienced folks. So here goes...
I am in charge of a project that needs to log customers incidents for reporting purposes. I have x number of customers who each need their own incrementing serial to identify their own incidents. I assume I'll need a customer table, but really not sure where to start.
The incident number would be: incident date + customer ID + incrementing serial
The incident date will be on the main incident entry screen so that's where that should be pulled from, as will a customer field (drop down list?) and the incident number itself. The customer ID (which is not the same as customer name, but an abbreviation referenced in the customer table) will be in the customer table as will the serial I assume.
Bonus! I would like to be able to reuse a customers incident serial number when an incident gets cancelled for what ever reason. It should be flagged as "CAN" and at the same time that number gets put back into the pot to get reused. I suppose I'd build a "CANCEL" button for this. This way I have an actual count of incidents that does not include cancellations.
If I'm not clear, by all means, let me know. If I need to throw in a screen shot of my layout, I can do that too.
Thank you in advance for any help.