INCREMENT THEN INSERT A FIELD INTO TABLE??? 1

[rpangel]

hi everyone,
i'm trying to increment a previous value in a table and then insert in the same column when the query is executed. for example:

number description
6001 blah blah blah
6002 ..... <---last and largest existing value in table
<END OF TABLE>

i want 6003 to be inserted in to the field after being incremented when the query is executed so when the query is executed the next row would be..

number description
6001 blah blah blah
6002 ...........
6003 blah blah blah <--- new number to use

i tried to use an update stmt but it replaced the previous value. i want it to keep a record of the other values but just insert the new incremented value.

i tried
update office_code
set number = number + 1
of course it didn't work

any suggestions? thanks in advance if u do,
angel

 

[MichaelRed]

Well, in code, you need to add the new record before you set the [number] value. Depending on the program version and recordset type you are using, the syntax would be somewhat different.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[tlbroadbent]

INSERT INTO tbl ( [number], office_code )
SELECT [number]+1 AS Expr1, tbl.Office_Code
FROM tbl
WHERE (((tbl.Number)=(Select max([number]) From tbl)));
Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it if you have time.