best way to create an Order Number

[ShaunG]

I thought I had it sussed but maybe not so much....



I want to create an Order number which automatically populates when a new order form is opened.... but the tricky part is im trying to keep this number as small as I can while combining, not adding the - Job Number, UnitID, and ItemID = Order Number.

We can have Multiple units per Job and Multiple Items per Unit, but not more then 100 for either.

every new Job Number Im trying to get the UnitID To start at 1 and every New UnitID to start at 1 also.... otherwise the order Number will get massive real quick, also I im trying not to add the number together I want to combine it.

example - Job Number 1112, UnitID 1, and ItemID 1 = 1112001001 the reason for combining and not adding is you can use the number to work out which job, unit and order it is for.

is this the best way to achieve what I want.... or is there a better way to get an order number that wont repeat itself and is small.

this is what I thought

=[tblJobDetails]![JobNumber]*100+[tblJoineryUnit]![UnitID]*100+[tblOrders]![ItemIID]

[SodaPop]

I thought I had it sussed but maybe not so much....

I want to create an Order number which automatically populates when a new order form is opened.... but the tricky part is im trying to keep this number as small as I can while combining, not adding the - Job Number, UnitID, and ItemID = Order Number.

We can have Multiple units per Job and Multiple Items per Unit, but not more then 100 for either.

every new Job Number Im trying to get the UnitID To start at 1 and every New UnitID to start at 1 also.... otherwise the order Number will get massive real quick, also I im trying not to add the number together I want to combine it.

example - Job Number 1112, UnitID 1, and ItemID 1 = 1112001001 the reason for combining and not adding is you can use the number to work out which job, unit and order it is for.

is this the best way to achieve what I want.... or is there a better way to get an order number that wont repeat itself and is small.

this is what I thought

=[tblJobDetails]![JobNumber]*100+[tblJoineryUnit]![UnitID]*100+[tblOrders]![ItemIID]

I'll always say don't do that. Just use autonumber unless for example job 900 always = "brakes" or some other static value. No good reason to attempt to make a p.o. smart.

Sent from my SM-G950U using Tapatalk

[ShaunG]

I'll always say don't do that. Just use autonumber unless for example job 900 always = "brakes" or some other static value. No good reason to attempt to make a p.o. smart.

Sent from my SM-G950U using Tapatalk

I was really hoping to use it.... using auto number the order no in a year or 2 will be massive

have you experience a lot of problems with what im trying to do.

[orange]

using auto number the order no in a year or 2 will be massive

How many Orders per year do you anticipate?
Do yourself a real favor--DO NOT TRY TO CODIFY/CONCOCT A CODE WITH INTELLIGENCE!!!

Relational database works very well (as designed) 1 fact 1 field.
If you must display some combination, you can concatenate the individual fields(for presentation).

Read the attached file for guiding database principles.

[SodaPop]

I was really hoping to use it.... using auto number the order no in a year or 2 will be massive

have you experience a lot of problems with what im trying to do.

Yes, you end up painted in corner and constantly have to conform to your own rules as the business changes. It becomes problematic. What if you change job Id's or item Id's?

100% of major outfits I've worked around like Wal-Mart and JB Hunt use standard PO incrementing. Well Wal-Mart did have Distribution center in their Po's up until a couple years ago but they dropped it.

The reality is the number means nothing. It is a way to link detail and that is it. Someone might say, what was the cost on PO 12345 and you can look it up which is easier than looking up work done on Tuesday for customer y. That is the only purpose, a reference.

I would just run a 10 digit auto number and start over at 9999999999. That's a lot of PO's. I bet you can go quite few year on them. Starting over is not an issue if you remember the p.o. is just a reference. I worked for 1 company with so much activity we'd see the same p.o. a couple times a year. It isn't a problem.

Short version is there is no good reason to use a smart p.o. in 90% of applications and it us way easier to use autonumber.

But you have to do it how it's best for you. If you want to concatenate the numbers I would do it as a string instead of number, not sure you'll get what you want adding them.

Sent from my SM-G950U using Tapatalk

[ShaunG]

How many Orders per year do you anticipate?
Do yourself a real favor--DO NOT TRY TO CODIFY/CONCOCT A CODE WITH INTELLIGENCE!!!

Relational database works very well (as designed) 1 fact 1 field.
If you must display some combination, you can concatenate the individual fields(for presentation).

Read the attached file for guiding database principles.

we do any where from 6 -20 per day which would = to 1500 - 5000 per year (working days)

Working it out on paper, I guess its not that bad - 5 digit order number for roughly 20 years then 6 digit number after that.... that's working it out at max

the main reason for what I wanted was to easily look at an order and no what its for.

DO NOT TRY TO CODIFY/CONCOCT A CODE WITH INTELLIGENCE - so adding multiple fields to get an outcome in other words, ive done my best at normalisations and keeping the forms correct with the help of other reading as well.

If you must display some combination, you can concatenate the individual fields(for presentation). - what do you mean by this can it record the outcome back to table or purely for looks at the time?

[ShaunG]

Yes, you end up painted in corner and constantly have to conform to your own rules as the business changes. It becomes problematic. What if you change job Id's or item Id's?

100% of major outfits I've worked around like Wal-Mart and JB Hunt use standard PO incrementing. Well Wal-Mart did have Distribution center in their Po's up until a couple years ago but they dropped it.

The reality is the number means nothing. It is a way to link detail and that is it. Someone might say, what was the cost on PO 12345 and you can look it up which is easier than looking up work done on Tuesday for customer y. That is the only purpose, a reference.

I would just run a 10 digit auto number and start over at 9999999999. That's a lot of PO's. I bet you can go quite few year on them. Starting over is not an issue if you remember the p.o. is just a reference. I worked for 1 company with so much activity we'd see the same p.o. a couple times a year. It isn't a problem.

Short version is there is no good reason to use a smart p.o. in 90% of applications and it us way easier to use autonumber.

But you have to do it how it's best for you. If you want to concatenate the numbers I would do it as a string instead of number, not sure you'll get what you want adding them.

Sent from my SM-G950U using Tapatalk

yeah my problem was I actually didn't do the maths on paper with auto numbers and it isn't as bad as I originally thought..... so im happy to go with that

does auto number go endlessly?

if there are double ups of an order number when you try to filter it isn't that where the issues will lie?

[Minty]

An Autonumber Primary key will always be unique.
You can't guarantee that it will be sequential, but again when you stop and think about it, that doesn't actually matter either.

Like all the other posters have said, and you have accepted - it's just an identifier, keep it simple and your life as database twiddler will be also be kept simple.

[ShaunG]

An Autonumber Primary key will always be unique.
You can't guarantee that it will be sequential, but again when you stop and think about it, that doesn't actually matter either.

Like all the other posters have said, and you have accepted - it's just an identifier, keep it simple and your life as database twiddler will be also be kept simple.

simply and working is what im after... cheers for the advise