numbering my records

[keesb]

I have a table with a field with projectsnumbers in it. Now I need to
created records for the next year and the projectsnumbers must be automatic
generated by counting +1

I want to use a query but how can I do so?

I dont want to use the autonumber option because f.e. my last number was
2589 and my next number must be 2590 and so on.

Kees

 

[Pat Hartman\(MVP\)]

Record order has no meaning in a relational database. What is the purpose
of the sequential number? It is quite easy to generate 1-up numbers in a
report.
1. add a control
2. set its ControlSource to:
=1
3. set its running sum property to:
Over All

Generating sequential numbers in a query is EXTREMELY inefficient and
REQUIRES a unique identifier that provides the necessary ascending order.
An autonumber can be used for this purpose. Using a query with a DCount()
is one method.
Select DCount("*", "YourTableOrQuery", "YourAutonumber < " & YourAutonumber)
As SeqNum, ....
From YourTable
Order by YourAutonumber;

 

[John Vinson]

I have a table with a field with projectsnumbers in it. Now I need to
created records for the next year and the projectsnumbers must be automatic
generated by counting +1

I want to use a query but how can I do so?

I dont want to use the autonumber option because f.e. my last number was
2589 and my next number must be 2590 and so on.

Kees

If I understand aright, you would like a project number that works
sort of like an autonumber, but that starts over at the new year...
right?

One way to do this is to use a Form to enter the data (table
datasheets don't have usable events and should not be used routinely
in any case!). In the Form's BeforeInsert event put code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtProjectNumber = NZ(DMax("[ProjectNumber]", "[Projects]", _
"[ProjectYear] = Year(Date())")) + 1
Me.Dirty = False
End Sub

This will look up the largest existing project number for the current
year (guessing that you have a ProjectYear field); if it's the very
first project for a year this will be NULL, and the NZ function will
return 0. It then adds 1 to this number and stores it in the form
control txtProjectNumber (which should of course be bound to the
ProjectNumber field).

The Me.Dirty = False immediately writes the record to disk so that
another user won't grab the same project number while you're working
on the rest of the fields in the record.

John W. Vinson[MVP]

 

[keesb]

I'm sorry if my info was not compleet.
The reasen i have to do so is the following:

I have a tabel with all my projects over 2005. Now I want to copy all my
running projects to a new record for the year 2006.
I can do so with a edding query. But al those projects has an unique
projectnumber. For every new project in the year 2006 the projectnumber must
be up +1.

So their are 500 projects from number 1000 to 1500 for the year 2005. Those
must be copied to new projects with number 1501 tot 2000 for the year 2006.

If I have to do this in a form it's to many work.

regards

Kees

 

[Guest]

Pat: Granted that record order has no meaning, but record NUMBERING is a very
common business requirement. Invoices, checks, orders, shipments, jobs,
projects, help requests, etc, etc, all typically need to be numbered, and
often audit trail requirements are that the numbering be consecutive (no
gaps). That is how I understand keesb's issue.

Keesb: You are right that autonumber is not applicable, because it does not
guarantee consecutive numbering. A simple way to generate consecutive numbers
is to query the project table for MAX(ProjectNo), add 1 to it and use that
for the new project. The project no. field must be uniquely indexed. This
will work in a multi-user environment, altough with serious performance
issues if there is heavy concurrent creation of new projects. Example: Say
the max project number is 123, and several users read it. Each user
increments it to 124 and attempts to write a new project record. Only one
user will succeed, the others will fail due to duplicate value in a unique
index. They can then retry by re-reading the max (now 124) and incrementing
it by 1, etc. You can see that if there are many concurrent users, it may
take many tries for a particular user to get his record inserted. For heavy
concurrent use, you should consider a SQL Server back end which can implement
very robust transactional processing.

 

[John Vinson]

I'm sorry if my info was not compleet.
The reasen i have to do so is the following:

I have a tabel with all my projects over 2005. Now I want to copy all my
running projects to a new record for the year 2006.
I can do so with a edding query. But al those projects has an unique
projectnumber. For every new project in the year 2006 the projectnumber must
be up +1.

So their are 500 projects from number 1000 to 1500 for the year 2005. Those
must be copied to new projects with number 1501 tot 2000 for the year 2006.

If I have to do this in a form it's to many work.

I would VERY strongly suggest that you do NOT need to do this!

This would entail storing data redundantly, with *different* unique
identifiers. If you're working on a project this Friday, and then do
some more work on it next Tuesday - it's still the *same* project; why
is it necessary to assign it a new project number, and (worse) store
the information about it in a second record?

John W. Vinson[MVP]

 

[keesb]

It's more complex as you think.

Firsth, I don't create a project every week but every year.

Second, their is a related table with prices per our.
In this table their are several sort of ourprices, f.e. normal ourprice,
evening ourprice, weekend ourprice and so on.

So when someone bookes his working ours on a project, he can selected the
sort of our he worked. At that moment their is a look-up from the project in
the our table to see what price belongs to that project for that kind of
our.

So every year their must be new projects with related new our records.
Thirst a want to create the new projects and then the new ourrecords in that
table witch has a unic key (year&[ProjNo]&[OurSort])

With this system it is possible to book ours in projects for the year 2005
en 2006 depending on the price agrement.

This is the reason I need to created new projects automaticly.

 

[John Vinson]

It's more complex as you think.

Firsth, I don't create a project every week but every year.

Sure. That's what I meant: Friday is December 30, 2005; next Tuesday
is January 3, 2006. Different years.

Second, their is a related table with prices per our.
In this table their are several sort of ourprices, f.e. normal ourprice,
evening ourprice, weekend ourprice and so on.

The price should be dependent on the date, not on the project
number... no?

So when someone bookes his working ours on a project, he can selected the
sort of our he worked. At that moment their is a look-up from the project in
the our table to see what price belongs to that project for that kind of
our.

So change the lookup to lookup the price as of a particular date.
Surely you don't want to constrain your business so that you can ONLY
change prices on January 1?

So every year their must be new projects with related new our records.
Thirst a want to create the new projects and then the new ourrecords in that
table witch has a unic key (year&[ProjNo]&[OurSort])

With this system it is possible to book ours in projects for the year 2005
en 2006 depending on the price agrement.

This is the reason I need to created new projects automaticly.

Well... you can run an Append query; if you want to append the
existing project records with new numbers, you can determine what
number you must add to the existing project number to get a new
project number. Create a query selecting the 2005 projects; put in a
calculated field

NewProjectNumber: [ProjectNumber] + 456

or whatever the number is. Change this to an Append query and run it.

John W. Vinson[MVP]