Solved Autonumber Advice (1 Viewer)

ChrisMore

Member
Local time
Today, 03:47
Joined
Jan 28, 2020
Messages
89
Hi,

I have created a company stock database which is working well but I am looking to expand its functionality to more of a customer orders database with the stock side of things being a feature rather than its primary use.

I have been updating the customer order numbers manually based on order paperwork which I receive from the sales department (the paperwork isn't necessary in order number order). This is working fine at the moment, however, we are looking to change the database so the sales team enter the customer order information and the database autonumbers the order number. I understand the number field can be changed to autonumber using an append query (if there is a better way of doing it then please let me know).

The real purpose of this post is to ask for advice on how to approach our order number structure. If an order is split into multiple deliveries we place a letter after the order number i.e. if the order number is 1000 and is split into 3 deliveries we would have order numbers 1000A, 1000B and 1000C. Of course this won't work with the autonumber in place but I am struggling to come up with a solution to allow the order to be split whilst having an autonumber in place.

Any suggestions will be greatly appreciated.

Thanks,
Chris
 

Gasman

Enthusiastic Amateur
Local time
Today, 03:47
Joined
Sep 21, 2011
Messages
7,172
I only ever use autonumber to link records.
Plenty of questions already on this site into how to create 'company' keys., but the 'real' key is the autonumber, the company key is for users to view.?
I would create a function that looks for the number of deliveries already for that order and increments accordingly.
I would probably prefer 1000-1, 1000-2 etc, but easy enough to return an apha character and pray no more than 26 deliveries. :)
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Yesterday, 21:47
Joined
Feb 28, 2001
Messages
18,379
The thing to remember is that an autonumber is hardly ever visible to anyone. It is most often a behind-the-scenes value. The moment that you add some kind of practical interpretation to an autonumber, you break it. The ONLY time I ever showed the autonumber was as a confirmation number. The difficulty only gets worse when you talk about having some kind of "split" order that would have the same number - because with an autonumber, that just about can't happen.

I think what many people would do here to keep a "Master Order" table that maybe has minimal detail information, just the customer ID and date and a few other issues, then create a child table of the master order that has a sequence code. The prime key of the master order table would be the autonumber of that table. But that "segmented order" key you talked about might be in the child table, where you have a sequence number (restarting from 1 for each new order). If you split the order, the 2nd part is sequence #2, 3rd part is sequence #3, and the prime key of THIS table is the compound key of the master order number (which is in this table, when in isolation, a foreign key) and the order sequence number. And in this context, you could format a field in reports and on forms to show both fields separated by a dash or something like that.

That would make the details table sometimes only have one child of an order (if it stayed simple) but you could literally have dozens of splits if needed for a really complex order.

You could think of it as an invoice/line-item situation, which is what you would see more often in this forum, and the keyword might be "invoice" to get you to some of those articles.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:47
Joined
Feb 19, 2002
Messages
30,148
Do you sell only a single product? When an order is split into multiple deliveries, how are the products distributed if an order could contain multiple products? Or if an order can contain only a single product, how are the quantities distributed? I'm trying to work out if you actually need an order table and a delivery table or if you should just have an order table. We don't know enough about your business to decide if there should be a delivery table between the order table and the order items table.
 

Isaac

Lifelong Learner
Local time
Yesterday, 19:47
Joined
Mar 14, 2017
Messages
2,817
as others have said or implied don't mix or confuse these two things:

- the autonumber whose purpose is a Key value, primary or foreign, and doesn't have any business value or meaning or interpretation of any kind
- any other user-facing value (they can call it a Key if they like--but you'll know it really isn't) that the business wants, like 1000B, or, 2020-10-1234
 

ChrisMore

Member
Local time
Today, 03:47
Joined
Jan 28, 2020
Messages
89
You may get some ideas from the data models at Barry Williams' site. Here is one on Customers, Orders and Partial Deliveries.
Good luck.
Thanks for the link, this looks great as guide but as I have already followed Allen Browne's Inventory Control: Quantity on Hand guide I am sort of forcing it to work with what I already have. I have added a shipments table and it's looking promising but now the problem is getting it to work with Allen Browne's code...
 

ChrisMore

Member
Local time
Today, 03:47
Joined
Jan 28, 2020
Messages
89
I only ever use autonumber to link records.
Plenty of questions already on this site into how to create 'company' keys., but the 'real' key is the autonumber, the company key is for users to view.?
I would create a function that looks for the number of deliveries already for that order and increments accordingly.
I would probably prefer 1000-1, 1000-2 etc, but easy enough to return an apha character and pray no more than 26 deliveries. :)
The thing to remember is that an autonumber is hardly ever visible to anyone. It is most often a behind-the-scenes value. The moment that you add some kind of practical interpretation to an autonumber, you break it. The ONLY time I ever showed the autonumber was as a confirmation number. The difficulty only gets worse when you talk about having some kind of "split" order that would have the same number - because with an autonumber, that just about can't happen.

I think what many people would do here to keep a "Master Order" table that maybe has minimal detail information, just the customer ID and date and a few other issues, then create a child table of the master order that has a sequence code. The prime key of the master order table would be the autonumber of that table. But that "segmented order" key you talked about might be in the child table, where you have a sequence number (restarting from 1 for each new order). If you split the order, the 2nd part is sequence #2, 3rd part is sequence #3, and the prime key of THIS table is the compound key of the master order number (which is in this table, when in isolation, a foreign key) and the order sequence number. And in this context, you could format a field in reports and on forms to show both fields separated by a dash or something like that.

That would make the details table sometimes only have one child of an order (if it stayed simple) but you could literally have dozens of splits if needed for a really complex order.

You could think of it as an invoice/line-item situation, which is what you would see more often in this forum, and the keyword might be "invoice" to get you to some of those articles.
as others have said or implied don't mix or confuse these two things:

- the autonumber whose purpose is a Key value, primary or foreign, and doesn't have any business value or meaning or interpretation of any kind
- any other user-facing value (they can call it a Key if they like--but you'll know it really isn't) that the business wants, like 1000B, or, 2020-10-1234
I was thinking an autonumber was the way to go as my colleagues would require the database to generate a unique order number (our reference number for the order). Is there a better way of doing this then instead of using an autonumber? As gasman said, it is more of a 'company key' which we will use as a reference, and has more value than a 'behind the scenes' number.
 
Last edited:

ChrisMore

Member
Local time
Today, 03:47
Joined
Jan 28, 2020
Messages
89
Do you sell only a single product? When an order is split into multiple deliveries, how are the products distributed if an order could contain multiple products? Or if an order can contain only a single product, how are the quantities distributed? I'm trying to work out if you actually need an order table and a delivery table or if you should just have an order table. We don't know enough about your business to decide if there should be a delivery table between the order table and the order items table.
The order could be for multiple products, but the quantities of each product ordered could be split into multiple deliveries of varying amounts. We work in manufacturing for construction so the customer might order a bulk amount but want specific quantities delivered sporadically when required on the construction site.
 
Last edited:

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Yesterday, 21:47
Joined
Feb 28, 2001
Messages
18,379
The only problem you have with autonumbers being visible is if someone could somehow interpret them as having meaning. As far as generating a unique number for the order, that is what autonumbers do - but the catch is what you later try to do with it.

Here is a case where you would NOT use an autonumber. In an invoicing system, some places are under accounting rules that do not allow gaps in invoice numbers so that they can verify continuity as a way to know that invoices haven't been hidden or left out. Autonumbers do not guarantee continuity because of when and how they are allocated. Therefore, the autonumber cannot be used for the invoice number - because there are rules on the invoice number.

That is the kind of thinking that will tell you whether to use autonumber or a number generated by "DMax() +1" methods. YOU are the person who knows your site needs and rules - none of us do. So that decision is yours to make.
 

ChrisMore

Member
Local time
Today, 03:47
Joined
Jan 28, 2020
Messages
89
The only problem you have with autonumbers being visible is if someone could somehow interpret them as having meaning. As far as generating a unique number for the order, that is what autonumbers do - but the catch is what you later try to do with it.

Here is a case where you would NOT use an autonumber. In an invoicing system, some places are under accounting rules that do not allow gaps in invoice numbers so that they can verify continuity as a way to know that invoices haven't been hidden or left out. Autonumbers do not guarantee continuity because of when and how they are allocated. Therefore, the autonumber cannot be used for the invoice number - because there are rules on the invoice number.

That is the kind of thinking that will tell you whether to use autonumber or a number generated by "DMax() +1" methods. YOU are the person who knows your site needs and rules - none of us do. So that decision is yours to make.
Thank you for your advice, I think using the DMax method is the way to go. I've just got it working in the database and that is exactly what I need.

I am still trying to solve my issue with having an order split into multiple deliveries. I'm not sure if I misunderstood your suggestion but would it work if there are multiple products on the same order? My database currently has a 'Master Order' table (Customer_Orders) and a child table (Customer_Order_Items). The order items table allows the user to enter multiple products under the same order number.

I have created a shipments table (using the data model link jdraw posted earlier) and this could enable me to split the products ordered based on the "Order Shipment Number" assigned to each product. The problem is I think this would be easy to break if the Order Shipment Number is incorrectly selected/entered. I also have the problem of updating the code to get this working (I took the code from Allen Browne's Inventory Control: Quantity on Hand guide so don't feel confident in updating it to get it to work with the shipments table).

Thanks,
Chris
 

Isaac

Lifelong Learner
Local time
Yesterday, 19:47
Joined
Mar 14, 2017
Messages
2,817
I was thinking an autonumber was the way to go as my colleagues would require the database to generate a unique order number (our reference number for the order). Is there a better way of doing this then instead of using an autonumber? As gasman said, it is more of a 'company key' which we will use as a reference, and has more value than a 'behind the scenes' number.
Sorry if it came across as though I were discouraging the use of AutoNumber in general; I was not intending to do that.

- Use AutoNumber for the real primary key behind the scenes
- In addition, Use something else, IF your users want a user-facing "key" of some type with requirements of any kind as to how it should look, compute, or be derived by some logic
- That said, if your users are quite fine "seeing" the real primary key AutoNumber and utilizing that for any and all of their purposes (as well as the real primary key behind the scenes), there is nothing wrong with that at all. Obviously just make sure they understand it will never be editable and you will never change it.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:47
Joined
Feb 19, 2002
Messages
30,148
Here's an example that shows two uses of a generated sequence number. One is used as a unique identifier for the record and is the visible, customer-friendly identifier. The other is a sequence number for line items that might need to be resequenced. This example includes a renumber button.

You will need to add an additional table to the schema to separate the shipments. I'm not sure what the proper structure is. Having shipment below the line items is awkward since not all items and not all orders will be split. Let us know what you end up with. It might need to be a mirror structure. But, in the long run, I would probably end up by deciding that each shipment should be a separate order. I'll have to think on this.

I can think of two reasons for wanting a "group" that combines all the shipments and they are
1. a single combined invoice
2. quantity breaks

I really don't know enough about the business to make a determination on how best to do this.
 

Attachments

  • CustomSequenceNumber20201020b.zip
    78.9 KB · Views: 22
Last edited:

ChrisMore

Member
Local time
Today, 03:47
Joined
Jan 28, 2020
Messages
89
Sorry if it came across as though I were discouraging the use of AutoNumber in general; I was not intending to do that.

- Use AutoNumber for the real primary key behind the scenes
- In addition, Use something else, IF your users want a user-facing "key" of some type with requirements of any kind as to how it should look, compute, or be derived by some logic
- That said, if your users are quite fine "seeing" the real primary key AutoNumber and utilizing that for any and all of their purposes (as well as the real primary key behind the scenes), there is nothing wrong with that at all. Obviously just make sure they understand it will never be editable and you will never change it.
No need to apologize, I am still learning Access so finding out the best way or alternative ways to use certain functions is fine by me.

Thanks,
Chris
 

ChrisMore

Member
Local time
Today, 03:47
Joined
Jan 28, 2020
Messages
89
Here's an example that shows two uses of a generated sequence number. One is used as a unique identifier for the record and is the visible, customer-friendly identifier. The other is a sequence number for line items that might need to be resequenced. This example includes a renumber button.

You will need to add an additional table to the schema to separate the shipments. I'm not sure what the proper structure is. Having shipment below the line items is awkward since not all items and not all orders will be split. Let us know what you end up with. It might need to be a mirror structure. But, in the long run, I would probably end up by deciding that each shipment should be a separate order. I'll have to think on this.

I can think of two reasons for wanting a "group" that combines all the shipments and they are
1. a single combined invoice
2. quantity breaks

I really don't know enough about the business to make a determination on how best to do this.
Thanks for the example. I have only started to use Access in the last year so I am struggling to make sense of how I can implement it into my database. However, I can see how the 'CustomSeqNum' in your example could be used in my database to generate the shipment number.

At this time I think it is best to share with you what I have so far. I would share the whole database but it does hold a lot of confidential data. I have attached a screenshot of my customer orders form and another of the database relationships.

I have a table for Customer_Orders, Customer_Orders_Items and Shipments. The relationship between these works so the user can create multiple shipments under one order number if the order is split into multiple parts, or just one shipment if the order is to be delivered in full. The user can then select which shipment each product is on under 'Order Shipment Number' of the 'Bill of Materials' section. At the moment this field is a combo which lists all shipment numbers so it would work better if it only listed the shipment numbers associated with each order.

The problem with this set up is that the user could select the wrong shipment number for a product and it will mess up the inventory calculation. The inventory calculation uses the 'Production Complete Date' field as the identifier to whether to reduce the stock for the product. Therefore, my code will need to be amended so it only includes in the inventory calculation the products in the 'Bill of Materials' section assigned to a shipment number that has a 'Production Complete Date' set. As I have previously said, I took the code from Allen Browne's Inventory Control: Quantity on Hand guide so don't feel confident in updating it to get this system to work.

Let me know if you need anymore information about my database.

Thanks for your assistance,
Chris
 

Attachments

  • Customer Order Form Screenshot.png
    Customer Order Form Screenshot.png
    101 KB · Views: 25
  • Database Relationships Screenshot.png
    Database Relationships Screenshot.png
    121.9 KB · Views: 23

ChrisMore

Member
Local time
Today, 03:47
Joined
Jan 28, 2020
Messages
89
As an alternative to the above, now that I'm not using autonumber for the order number I have a bit more flexibility with the number structure. It's not an issue if each part delivery is on the Customer_Orders table as a separate record however each part will need to be referenced A,B,C etc. With this in mind, how could I use the DMax function and also use the letter reference as it will have a type mismatch? I did want to lock the order number field from editing so the user can't manually enter the order number, but I don't know if that will be possible considering a letter reference could be required for a split order.

Thanks,
Chris
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 03:47
Joined
Sep 21, 2011
Messages
7,172
If you have the delivery table linked to the order table, you will already have your user ordernumber. Then in the delivery table if you have a field for A, B,C etc, then you get that Max for the autonumberID. you would still have to work out if it is D, then the next is E, but you could hold the ASCII code instead and increment that and then use the CHR() function for that number?
It is recommended to keep these part of whatever separate, and concatenate them, rather than trying to split them up all the time.

HTH
 

ChrisMore

Member
Local time
Today, 03:47
Joined
Jan 28, 2020
Messages
89
If you have the delivery table linked to the order table, you will already have your user ordernumber. Then in the delivery table if you have a field for A, B,C etc, then you get that Max for the autonumberID. you would still have to work out if it is D, then the next is E, but you could hold the ASCII code instead and increment that and then use the CHR() function for that number?
It is recommended to keep these part of whatever separate, and concatenate them, rather than trying to split them up all the time.

HTH
Hi Gasman,

I am not familiar with ASCII code so I'm not sure what you mean. If I'm being honest I don't really understand your idea at all (down to my lack of knowledge on some of the terminology you've used). Would you be able to explain it again?

Thanks,
Chris
 

Gasman

Enthusiastic Amateur
Local time
Today, 03:47
Joined
Sep 21, 2011
Messages
7,172
Every character has an ASCII code http://www.asciitable.com/
If you try the code below in the immediate window it will show
Code:
? asc("A")
65
conversely if you try
Code:
? chr(65)
A
so if your next number was 7, you would add that to 64 (to get the correct alpha character) and would get
Code:
? chr(71)
G
which is the 7th character in the alphabet.

Any clearer now?
 

Zedster

Registered User.
Local time
Today, 03:47
Joined
Jul 2, 2019
Messages
105
I would be tempted to look at your problem from a different perspective. It sounds like each order can have multiple deliveries. I would be inclined to have a child table for deliveries rather than split a single order up into 3 separate orders. If you still wish to have separate orders with suffixes I would generate the incremental number as a text field using vba.
 

Users who are viewing this thread

Top Bottom