Re: Update a number field incrementally

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!

--
Sam

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200602/1
.

Relevant Pages

  • Re: Update a number field incrementally
    ... *separate fields* which can be concatenated as needed for display. ... 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)
  • 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)
  • =?iso-8859-1?Q?Re:_>27x132=3F?=
    ... The sizes picked up by a BIND with the query bit are determined by the ... His idea is simply to be able to have a default set of dimensions other ... it's in reference to applications that can handle more than ...
    (bit.listserv.ibm-main)
Loading