Formatted Autonumber

[Greg McLandsborough]

I have a Table the contains a Document_ID, Project_ID and a Document_Number.
The Document_Number is a more user friendly (descriptive) version of the
Autonumber sortof. It contains a Discpline_ID and a Document_Type_ID and
sequential Number. The sequential number is not equal to the Autonumber, It
is instead the next available number for that particular project, of that
discipline and type.

How do I create a query that will make a number of this format ?

Thanks for any help...

Greg

 

[Jeff Boyce]

Greg

Too many referents -- "a number of this type" -- which type, of those you
referred to?

Are you asking how to find the next sequence number for a
project/discipline/type combination?

If so, first create a Totals query that finds the
Max([YourSequenceNumberField]) for the criteria matching project, discipline
and type. Take a look at the SQL view of that query.

Then add one!

(another way would be to use the DMax() function)

Good luck

Jeff Boyce
<Access MVP>

 

[Greg McLandsborough]

Jeff,

Sorry I a bit thick... Can you expand further.

I am creating a drawing register, in which each drawing has a Discipline and
a Type and sequential Number. For example "ST-AS-001" would be a
Structural/Assembly drawing. (Could be General/notes for example), The
sequential number should start at 1 for each project.

Is this to complex, or is it posible? How do I create a Totals query?

Sorry to be a pain, and thanks for your help

Greg

Too many referents -- "a number of this type" -- which type, of those you
referred to?

Are you asking how to find the next sequence number for a
project/discipline/type combination?

If so, first create a Totals query that finds the
Max([YourSequenceNumberField]) for the criteria matching project,
discipline
and type. Take a look at the SQL view of that query.

Then add one!

(another way would be to use the DMax() function)

Good luck

Jeff Boyce
<Access MVP>

I have a Table the contains a Document_ID, Project_ID and a Document_Number.
The Document_Number is a more user friendly (descriptive) version of the
Autonumber sortof. It contains a Discpline_ID and a Document_Type_ID and
sequential Number. The sequential number is not equal to the Autonumber, It
is instead the next available number for that particular project, of that
discipline and type.

How do I create a query that will make a number of this format ?

Thanks for any help...

Greg

 

[Jeff Boyce]

Greg

A Totals query would just give you an idea of how Access determines the
highest (Max()) sequence number. First build a query with Project,
Discipline & Type, plus Sequence Number fields selected for display. Then
click on the Totals query button. Use GroupBy for the first three, and
Max() for the Sequence Number.

And I do think you're better off using the DMax() function, but you'll need
to write a little code ...

I'd recommend reading up on both Totals queries and the DMax() function --
examples are usually included in Access HELP.

--
Good luck

Jeff Boyce
<Access MVP>

Jeff,

Sorry I a bit thick... Can you expand further.

I am creating a drawing register, in which each drawing has a Discipline and
a Type and sequential Number. For example "ST-AS-001" would be a
Structural/Assembly drawing. (Could be General/notes for example), The
sequential number should start at 1 for each project.

Is this to complex, or is it posible? How do I create a Totals query?

Sorry to be a pain, and thanks for your help

Greg

Too many referents -- "a number of this type" -- which type, of those you
referred to?

Are you asking how to find the next sequence number for a
project/discipline/type combination?

If so, first create a Totals query that finds the
Max([YourSequenceNumberField]) for the criteria matching project,
discipline
and type. Take a look at the SQL view of that query.

Then add one!

(another way would be to use the DMax() function)

Good luck

Jeff Boyce
<Access MVP>

I have a Table the contains a Document_ID, Project_ID and a Document_Number.
The Document_Number is a more user friendly (descriptive) version of the
Autonumber sortof. It contains a Discpline_ID and a Document_Type_ID and
sequential Number. The sequential number is not equal to the

Autonumber,
It

is instead the next available number for that particular project, of that
discipline and type.

How do I create a query that will make a number of this format ?

Thanks for any help...

Greg