Use of query to add sequential numbers to a table.

My InvoiceLines table uses the autonumber field for the primary key.

But I need another auto-incrementing field for the sort field.

Can such a field be genererated in the query by means of an expression, and
if so which one?

Please help, Frank

Are you asking if you can dynamically generate a value/column in a query or
do you think you need to store the value? Do you have some field or fields
that will determine the new sort order?

--
Duane Hookom
MS Access MVP


"Frank Martin" wrote in message
...
My InvoiceLines table uses the autonumber field for the primary key.

But I need another auto-incrementing field for the sort field.

Can such a field be genererated in the query by means of an expression,
and
if so which one?

Please help, Frank

Frank,

Use the DMAX function, in conjunction with the NZ function, as below:

NZ(DMAX("SortOrder", "yourTable"), 0) + 1

This looks for the maximum value in the SortOrder field, then adds one to
it. If there are no valid entries in SortOrder, DMAX will return a NULL, so
you have to use NZ to convert that to a zero, before the addition.

HTH
Dale

"Frank Martin" wrote in message
...
My InvoiceLines table uses the autonumber field for the primary key.

But I need another auto-incrementing field for the sort field.

Can such a field be genererated in the query by means of an expression,
and
if so which one?

Please help, Frank

Thanks, I'll try this.


"Dale Fye" wrote in message
...
Frank,

Use the DMAX function, in conjunction with the NZ function, as below:

NZ(DMAX("SortOrder", "yourTable"), 0) + 1

This looks for the maximum value in the SortOrder field, then adds one to
it. If there are no valid entries in SortOrder, DMAX will return a NULL,
so
you have to use NZ to convert that to a zero, before the addition.

HTH
Dale

"Frank Martin" wrote in message
...
My InvoiceLines table uses the autonumber field for the primary key.

But I need another auto-incrementing field for the sort field.

Can such a field be genererated in the query by means of an expression,
and
if so which one?

Please help, Frank

I need something to act as an 'autonumber', in the field of my choice, even
when the PK is an autonumber.

I need it for the 'sorting field' and also for incrementing invoice numbers.



"Duane Hookom" wrote in message
...
Are you asking if you can dynamically generate a value/column in a query
or
do you think you need to store the value? Do you have some field or fields
that will determine the new sort order?

--
Duane Hookom
MS Access MVP


"Frank Martin" wrote in message
...
My InvoiceLines table uses the autonumber field for the primary key.

But I need another auto-incrementing field for the sort field.

Can such a field be genererated in the query by means of an expression,
and
if so which one?

Please help, Frank