Re: Update a number field incrementally

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.

"John Nurick" <j.mapSoN.nurick@xxxxxxxxxxxxxx> wrote in message
news:t2flv1pj95fe66420obvjv9mf708v407d3@xxxxxxxxxx
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
    ... the reference number and to have a master record of the number assigned. ... Here is one way of setting up an incrementing number: ... If six digits is adequate, I assume that the incrementing number will never ... digit reference will be fine (6 digit sequence plus 2 digit dept). ...
    (microsoft.public.access.queries)
  • Re: Update a number field incrementally
    ... My point was not about the number, but about the fact that you are appending ... Here is one way of setting up an incrementing number: ... of the reference number since the date of the order is not necessarily the ... digit reference will be fine. ...
    (microsoft.public.access.queries)
  • Re: relating two tables with common field but not seeing data in forms.
    ... >this should bring up the query design grid with your two tables shown. ... >database table when you get a new csv file. ... >> member datafields to each record. ... >> example, to keep it simple, one reference number field and name field. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Table data being deleted in query, help
    ... It probably is the query, ... > in the description fields from all the various reference tables that will be ... >>> ExpenseElement and UserID and ReportHdr. ... >>> to datasheet view, you can not delete a record) ...
    (microsoft.public.access.formscoding)
  • Re: Table data being deleted in query, help
    ... The query has the main table and those ... and delete records without affecting the sources from the reference tables. ... > integrity not checked, ... nor is the cascading deletes or edits checked. ...
    (microsoft.public.access.formscoding)