Change existing table when data format of a field changes (1 Viewer)

Hi,

I'm building a database for a small business. I have a table tblInvoices with a field InvoiceNumber. Currently this is Short text, because invoices are “numbered” with a combination of letters and numbers – R07-2017 for example. I’m planning on changing that to a format like 20180007, among other things, because I intend to automatically assign invoice numbers. I'll modify the numbering standard for 2018, because I can't make changes within a calendar year.

I’m searching for an optimal way to bring about those changes. It seems unintuitive to just change the numbering and store data that would be Long into Short text field. I can’t change the field type property because I have to store previous invoice numbers.

How does one go about doing that? Would it be better to add a new field to an existing table, to create a separate table, maybe even a relational table? I don’t know if I’m just overcomplicating things. It may be a simple question but I really hit a brick wall with this one.

Thank you for your suggestions,
Alja

If this is a new database, do NOT use InvoiceNumber as the PK. Use an autonumber and make all relationships on that autonumber. Call it InvoiceID. InvoiceNumber is a data field but in the Invoice table, business rules say that it should be unique and so you want a unique index on it to prevent duplicates. That said, once you have history that is text, you can't change the data type to numeric.

Put the code in now to check the date and use procedure1 for dates prior to 1/1/2018 and procedure2 for the rest.

20180007

Click to expand...

Also, in a database you store discrete pieces of data discretely. That is, each piece of data gets its own field. It seems you are making the year an invoice is made part of the invoice number field--don't.

It's fine to store that data, but do it in a seperate field (e.g. [InvoiceYear]) and store the actual invoice number seperately. Don't shoe horn data together.

Even further, you probably don't need to store the year portion at all. Do you have a field that stores when the invoice was created (e.g. [InvoiceCreateDate])? If so, you don't store the year at all, you just extract it from that date field.

I didn't want to go there because the old values are text and also need to coexist.

So separating the data parts correctly would mean that you have to store the value twice. Once as the InvoiceNumber (concatenated to allow for the old values) and a second time properly separated into two fields one of which might be the InvoiceDate rather than extracting the year() from that date and the other a numeric value that can be incremented.

Pat Hartman said:

If this is a new database, do NOT use InvoiceNumber as the PK. Use an autonumber and make all relationships on that autonumber. Call it InvoiceID. InvoiceNumber is a data field but in the Invoice table, business rules say that it should be unique and so you want a unique index on it to prevent duplicates. That said, once you have history that is text, you can't change the data type to numeric.

Put the code in now to check the date and use procedure1 for dates prior to 1/1/2018 and procedure2 for the rest.

Click to expand...

Hi Pat, thank you for your answer!

The InvNumber field is not PK, I don’t use text fields as PK.

I was thinking, what would be the downside of creating a new Invoices table, renaming the old one and using it as archive? We are talking about roughly 150 records. The db won’t be operational before the new year, so I can use the new table as recordsource for invoicing forms, but for the reports and for all other “display” purposes, I can build a query. And for the moment, I don’t have anything - any forms, queries… based on the Invoices table.

plog said:

Also, in a database you store discrete pieces of data discretely. That is, each piece of data gets its own field. It seems you are making the year an invoice is made part of the invoice number field--don't.

It's fine to store that data, but do it in a seperate field (e.g. [InvoiceYear]) and store the actual invoice number seperately. Don't shoe horn data together.

Even further, you probably don't need to store the year portion at all. Do you have a field that stores when the invoice was created (e.g. [InvoiceCreateDate])? If so, you don't store the year at all, you just extract it from that date field.

Click to expand...

Plog, thank you for pointing that out! I completely missed that . It’s interesting how the obvious things escape us sometimes .

It's not the number of records in the archive, it is remembering to include with the archive when you need it. I would not separate the data.
One option that will work as long as your BE is Jet/ACE is to use the autonumber as the numeric part of the InvoiceNumber. That way Access handles any conflicts and you won't have to worry about other users duplicating your generated ID. You can concatenate the year with the autonumber.

InvoiceNumber = Year(InvoiceDate) & Format(YourID, "000000")

If six digits isn't going to be enough, add more zeros to the format()

I've figured it out . As I predicted I was totally over-complicating things. As this is a new db, I don't have any real data in it. So what I'm going to do is have a number field for InvoiceNumber (it can't be autonumber because the invoices have to be sequentially numbered and begin with 1 each new year), with a composite index (I need to repeat numbers each year, but never in the same year). Then in a query, form, report I'll just use the concatenated string to serve as invoice number for display purposes. So, I don't need to change the numbering standard at all, yay! I don't know what I was thinking .

My Aha! moment happened while I was rereading your last post, Pat , so thank you very very much!

I would keep the invoice number stored as a string, then you do not have to make any changes.

a standard length y character invoice "number" format such as yyyynnn (year/sequence ref) will sort correctly whether it's text or numeric. An invoice number isn't meant to be managed as a number, so it shouldn't make any difference. If you think you might exceed 1000 invoices a year, then make it an 8 figure number.

A mushed field presents problems no matter what. You have problems when you create it and then you have problems when you use it. I understand that business users get fixated on adding "meaning" to their "numbers" and you probably aren't going to change their mind so save the fight for a battle you need to win.

Once you have agreed to do something which we all agree is poor practice, you need to make it as solid as possible. It is important to store the generated value because you will need to index it.

Always leave yourself some room in the "number" part of the ID. If they tell you they NEVER have more than 999 invoices in any given year, think about how happy they will be the first year they have 1000 and break your number format.

Keep in mind that you are generating numbers in a multi-user environment and therefore your code that does the +1 to get the next number has to be prepared to not be able to save the new number because someone else beat you to it. Access solves the problem with autonumbers by committing the new autonumber IMMEDIATELY. The downside of that is you can end up with gaps in the autonumber if records are started by not completed. Always generate the "number" as the last instruction in the BeforeUpdate event. This gives you the smallest possible window to run into contention but you STILL need to allow for it!!! and prepare the code to loop to generate a new "number"