Record Pre fix

[Gismo]

Hi all,

I have the below table
Each record should have its own prefix as per the sample

Please could you advise if possible in one single table

 

[The_Doc_Man]

Are you saying that you have essentially numbering starting from 0 for each of several possible document types?

This is possible but you are immediately asking for trouble to do it exactly as shown if that is a table. (Not if it is a query from a table with more fields in it - including your sequence number and prefix. The maintenance of these sequence numbers IF COMBINED IN A SINGLE FIELD will be more trouble than it is worth in the long run. You can have a separate sequence number and prefix that are combined via a QUERY and make that computation easy. The idea is that the numbers you show to the world don't have to be joined internally. They can be combined for display purposes but keep the numbers separate for easier manipulation.

Before we get too deep in this, a question: Are you in a situation where for some auditor will go crazy if you have non-continuous numbering? (We need to know if that is a requirement before we advise you.)

 

[Gismo]

Are you saying that you have essentially numbering starting from 0 for each of several possible document types?

This is possible but you are immediately asking for trouble to do it exactly as shown if that is a table. (Not if it is a query from a table with more fields in it - including your sequence number and prefix. The maintenance of these sequence numbers IF COMBINED IN A SINGLE FIELD will be more trouble than it is worth in the long run. You can have a separate sequence number and prefix that are combined via a QUERY and make that computation easy. The idea is that the numbers you show to the world don't have to be joined internally. They can be combined for display purposes but keep the numbers separate for easier manipulation.

Before we get too deep in this, a question: Are you in a situation where for some auditor will go crazy if you have non-continuous numbering? (We need to know if that is a requirement before we advise you.)

Hi Doc

The sequence starts a zero, when I do any transaction, I will add one first then use that in my document
No I dont need this for any auditing but I hear what you say, I will add the prefix only then printing the applicable document

 

[gemma-the-husky]

What you really want is the prefix in a different column, as below
Sorry, it's hard to align text on the site. There used to be a PHP format that helped.

DocumentType Prefix NextNumber
Purchase Order PO 1234
Goods Received GR 7643
Sales Order SO 2097
Invoice IN 756

 

[Pat Hartman]

Here is a working sample that shows how to generate a custom sequence number. The sample is more complicated than your request but it should help you work it out.

Custom Sequence Numbers

Two examples of a custom sequence number. One is a complex ID with concatenated parts. The other is just a sequence number for detail items and is not used as a PK. It has a renumber feature. See below

www.access-programmers.co.uk