Autonumber Question

G

Guest

I am currently building a database to manage the various projects that are
worked by field employees. I am wanting the project number to automatically
calculate based on the project type and the location that is working it. An
example of our project numbers is 16217-001 where 16 is the type of project
worked, 217 is the location number and 001 means this is the first project
for this location. As a result, I am wanting Access to generate the next
project number for this location of this type as 16217-002.

Any help is appreciated. Thank you.
 
G

Guest

The type of project and location are going to have to be user entered or
selected in some way.

You can then write a VBA function to return the recordcount of a
query/recordset pulling only ID's which include those type & location.

Or, you could write a query which returns the count of records matching that
type and location, or where they equal the left 5 characters of the ID in
your table.

Then just concatenate the # of records ( from either method ) + 1 to your
new ID value.
 
E

Evan Keel

darnett said:
I am currently building a database to manage the various projects that are
worked by field employees. I am wanting the project number to automatically
calculate based on the project type and the location that is working it. An
example of our project numbers is 16217-001 where 16 is the type of project
worked, 217 is the location number and 001 means this is the first project
for this location. As a result, I am wanting Access to generate the next
project number for this location of this type as 16217-002.

Any help is appreciated. Thank you.

Are you sure you want an "intelligent" project number? Is the project number
also a primary key? What if the location changes? Why not create a dumb key
like 1,2,3.....? Then have project type and project location as attributes
of project.

Evan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top