Update a number field incrementally

[AllyOop]

I have an orders table containing 4 fields:
Order number (text), order date, ship date, and Reference (number).
Orders are appended to the table daily, probably 1000 orders at a time.
The reference field is blank or 0 when the orders come in.
I would like to assign the reference number incrementally by 100, actually
sequentially with the two digit department number at the tail end....
(ie. 1026, 1126,1226 or 1051,1151,1251)

I have set up another table for the reference numbers and a second field for
the date that the number is used, so that I can track how many are used,
left, and that a number is not assigned twice.

I need the query to be able to run automatically regardless of how many
order there are each day. I am just having trouble setting up the update
query. I am not sure if I can do it all at once, or if I need a loop, (ie.
look for the first empty record, assign the lowest reference number, fill in
the date, start over)

Thanks!

 

[OfficeDev18 via AccessMonster.com]

Let's see... There are about 250 working days per year, and at 1,000 per clip
that means 250,000 records, more or less. That's already 6 places. A 2-digit
department number makes it 8 places, and a date field besides! Whew! Can I
make an alternate suggestion?

How about making your ID numbers "smart?" That is, it would include the date,
department number, and sequence - including total amount used per day? The
best part of it is that you never run out of numbers. What I am suggesting is
this: Make your ID Number 12 digits, as follows: yymmddssssDD, where "yy" = 2-
digit year, "mm" is 2-digit month, "dd" (as opposed to "DD") is 2-digit year,
"ssss" is 4-digit daily sequence - meaning 0001 through 9,999 - and "DD" is
the 2-digit department ID number. This obviates the need for a separate date
field, tells you how many were used per day, and records the department
number. Each day, "ssss" starts with 0001.

Of course, you would run this in VBA, so you can continuously calculate the
"ssss" component of the field, and format the whole thing correctly.

Makes sense?

Sam

 

[John Nurick]

Better to store the date, daily sequential number and department code in
*separate fields* which can be concatenated as needed for display.

 

[BruceM]

I understand the question to mean blocks of 1000 records are appended at
once. If so, I don't think that DMax or some other ususal system for
assigning incrementing numbers will work. Also, since each department's
number is supposed to start over from 0, any incrementing system will need
to take that into account. I would have to say that more information is
needed, including the source of the records that are being appended, and the
maximum number of records for one department in a single day.

 

[AllyOop]

Each day there will be any number of orders ranging from 10 to 1000 or more,
so they will not necessarily be blocks of 1000. I can't use the date as part
of the reference number since the date of the order is not necessarily the
ship date and this will confuse the issue. I had originally hoped to simply
use autonumber and concatenate this with the dept number, but am told that
autonumber is not necessarily reliable for sequencing. This is the reason for
setting up the reference table and pre-populating it with the numbers that I
want. Then I can just include the dept number in the table and increment the
numbers by 100. For this project I am working on, I am only setting this up
for one dept, so multiple depts is irrelevant, only significant for the fact
that I want the last two digits of the reference fields to be fixed. The
source is another access table, that contains the order # (text field) and
the appropriated dates (these are estimates, which is why I don't want to use
them as part of the reference number).
8 digit reference will be fine (6 digit sequence plus 2 digit dept).
Thanks!!

 

[BruceM]

My point was not about the number, but about the fact that you are appending
blocks of records. Here is one way of setting up an incrementing number:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

If the purpose of setting up the second table and appending records is for
the incrementing number, that is not necessary. If autonumber would have
worked except for the problem with gaps appearing in the sequence, then some
variant of the code in the link I provided will work.

I suggest that when confronted with a problem with which you need some help
it is fine to explain briefly what you have attempted so far, but an
explanation without constraints of the desired end result may prove most
beneficial. It is still not entirely clear if your sole reason for
appending records is because of the sequencing problem, or if there is some
other reason why this must be done. If you need the append query to loop
through records and assign an appropriate number, I'm afraid I cannot help
with that. I got into this conversation because I wondered if the usual
ways of assigning an incrementing number would work when appending blocks of
records.

If six digits is adequate, I assume that the incrementing number will never
start over, and that you do not anticipate a day when you will have more
than 999,999 records. Is this correct?

 

[AllyOop]

Hi Bruce,
Thank you for your prompt response. I previously posted several days ago
and was told that the best approach was to set up an outside table instead of
using autonumber, so I headed in that direction. I did come accross the
example you provided for incrementing a number, but was unclear how that
would be implemented when the orders were not added to the table through a
form one at a time.
Yes, I am appending them to this table, solely for the purpose of assigning
the reference number and to have a master record of the number assigned.
After that, I will send them into our main order system (which requires a
unique number for the primary key, which is the entire purpose of this
process).
The only reason I am not converting the text order number to a number and
using that because although unique, is assigned by the customer and we will
have no control over gaps and such and therefore would use more than needed
numbers.
I am not expecting more than 999999 orders in a short period of time, but
this field does have larger capacity and I could go higher, to 7 or 8 digits
without problems.
Hope this helps clarify my purpose and thank you again for all your help.

 

[BruceM]

I can only repeat that I do not know how to apply the incrementing number
when appending a block of records. If the sole purpose of the second table
is to assign the number, I would not have recommended heading in that
direction. Being unfamiliar with the technique, I cannot address questions
about it.
If you are committed to using the second table, about all I can suggest is
that you start each day by manually inputting the next number in the
sequence, and using incrementing code such as in the link I provided. Or
you could just leave the records in that table, and use a query to extract
one day's records. Of course, if you're doing that, you may as well use one
table. You could also use a query to combine the incremented number with
the department code, and send that number to the main order system.
By the way, I am not quite clear as to whether you are using a form to input
the orders. If not, none of the incrementing code will work.
I'm curious as to how you can guarantee that a customer-assigned number is
unique.