Re: Update a number field incrementally

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

On Mon, 20 Feb 2006 20:27:00 GMT, "OfficeDev18 via AccessMonster.com"
<u14095@uwe> wrote:


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

AllyOop wrote:
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!

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

.

Relevant Pages

  • Re: Update a number field incrementally
    ... make an alternate suggestion? ... and sequence - including total amount used per day? ... Order number, order date, ship date, and Reference. ... I need the query to be able to run automatically regardless of how many ...
    (microsoft.public.access.queries)
  • Usage of Referenced Patient/Visit/Study Sequence after retirement of Study Management Services?
    ... Referenced Patient Sequence ... With the removal of the description of the Study Management Services, ... but shall accept non-empty Reference ... MWL item into MPPS and DICOM Composite Objects, ...
    (comp.protocols.dicom)
  • Re: .99999... still=/= 1
    ... > decimal digit strings. ... diverging sum indeed have a limit as You consider a new point of reference. ... > sequence, and it is understood that any other sequence that happens to ... To describe a member of *N, for example, I can ...
    (sci.math)
  • Re: Complex Specified Information - Pitman Formula
    ... are based on non-random strings that are known to be produced by ... other reference you give. ... do muck it up with your first term, the size of total sequence space, ... You aren't claiming that your set of reference strings are able ...
    (talk.origins)
  • Re: Complex Specified Information - Pitman Formula
    ... other reference you give. ... How many times do I have to tell you that there is no "target" string ... do muck it up with your first term, the size of total sequence space, ... part of that equation does show us the degree of similarity of a test ...
    (talk.origins)