number to start from the last one entered and autonumber the rest

[haerion]

Hi all, I'm trying to get a column in my query to start from the last number in one of my table do a +1 on each line in the query, that is gonna be append to this same table, here is the code if I just choose the last number for each row:

INSERT INTO [PO Header] ( AccessID, Vendor, [Date], [Store#] )
SELECT Last([PO Header].AccessID) AS LastOfAccessID, [Import DFR with Store#].Vendor, Date() AS [Date], [Import DFR with Store#].[Store#]
FROM [PO Header], [Import DFR with Store#]
GROUP BY [Import DFR with Store#].Vendor, [Import DFR with Store#].[Store#];

I'm sure one of you have an easy way to go around that, hope I will be this good soon enough

 

[haerion]

So nobody have any idea how to do it, by coding or sql, or any other idea?

 

[AlexCuse]

First off, you can't really append to an autonumber field.

I have a collection of queries set up in db's where I may need to do this. I believe that I came up with them by following directions found here:

http://support.microsoft.com/kb/812718

If you have any questions about that post back.

Hope it helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[haerion]

Yeah, should not have talked about autonumber, the reality is that it is a numeric field in a table which i need to append between 10 to 200 line, which need to start off at the last number in the table +1, to do so, i think the best way would be to have a query get a number for each line like if the last number on the table is 45, then the query would start at 46 and go on like 47, 48, 49, 50 for each line, is this possible?

 

[AlexCuse]

Sure, give your table that you are appending from an autonumber. Each time you use this table, it should be recreated using a make-table query or other means (so autonumber starts at one). Then when setting up your append query, rather than appending the 'autonumber' value, append something like this:

[tblOne].[AutoNumberField] + (select max([NumberField]) from [tblTwo]) as NewNumField

Hope this will help.

A wise man once said
"The only thing normal about database guys is their tables".