Update and insert a increment number

T

Tom Ellison

Dear gv:

Your post implies there is some order to the rows. This happens only when
you sort them.

If you will supply me with the code for a query that displays these rows in
the order you want them numbered, I can add a column to that query that
supplies the incremental numbers.

You probably should not update the table to save this number. Every time a
row is added to the table, deleted, or when any of the sort columns is
modified, this ordering will change. It is therefore a very dynamic thing.
It would be better to number them as of the moment using just a SELECT
query.
 
G

gv

Hi again,

I want to add a number field to a existing table and insert the number 6015
in the first row and then increment
that number by one for each row after that?

thanks
gv
 
G

gv

hi and thank you for your help,

Let me try to explain a little, I'm trying to update a contact list table
from another table that has several hundred names.
The contact list table has a increment id field, that number is
generated by the user program so I need to start off
with the next number of what's in that table.
I have the insert query which works fine except for the first field I need
to start with the next number and then
add 1 to each row that is added.

So this is the query so far

Where I have the ? is where I need to get the next number from the contact
list table (column ekey).

INSERT INTO contact ( ekey, last_name, first_name, middle_name, function,
Active, prefix_ekey, create_date, update_date, endo_user_create_ekey,
endo_user_update_ekey )
SELECT ?, last, first, middle, '0', '1', '4', Format(Now(),"mm/dd/yyyy
h:mm:ss ampm"), Format(Now(),"mm/dd/yyyy h:mm:ss ampm"), '4099', '4099'
FROM contacts
WHERE prefix<>'' And last<>'' And first<>'';


thanks again for your help
gv
 
Top