I had built a group by query & sum all the amount column, which has 1000 records for which i need a sequential row number for it which will be used in another query for further processing any help on this
 thank you

Personally, I would change the query type into a Make Table query. Once the new table is created, I would go to design view, and add a column of type autonumber. Once save this field will automatically populate itself with sequential numbers.

Hope that helps,

Mike

Mike
EchoVue.com

Mike,

How about this - Create the table first with a column of type number.
Use vba to create a procedure.
1st. - Sql = "Delete * from Table;"

2nd. - Set a variable to 0.
3rd. - For each record in the main table,

SQl = "Insert into table variable+1, etc, etc, etc,"

What do you think? This way, He wouldn't have to add the autonumber everytime he created the table?

Kevin

dartcoach

I couldn't agree more, I generally do everything from VBA and avoid queries like the plague, I guess it just depends on the persons comfort with code. I have been working with some people this last week who cringe at the word "Code" so I am in the Non-VBA mindset currently.

This even brings up some other possibilities as well. Recently, I have been playing with Data Definition Queries which can change the table structure and design itself, I don't know if we could use any of that is this situation but you never know!! Alternatively, if you were to create a table with an autonumber field, you could clear it at the beginning of the function, and then insert into the second field and have Access populate the autonumber field as it goes. It took me a while to figure this out, but you have to specify the fields in the INSERT statement, before entering the values for it to work with an Autonumber field.

Civa - these are all good solutions - if you want more info on any of them, Kevin and I would be happy to elaborate!!

Mike

Mike
EchoVue.com

Mike,

I've never created a table inside code. The reason I use a straight number field, is that Access keeps track of the last autonumber used and starts at the next one, even if your table has been emptied. If you create a table with no records, and after your report is done, then empty it, you are only using enough storage to store the table structure. Also, Civa's report would always begin with 1.

Kevin

dartcoach

It must be late - the autonunber field not resetting slipped my mind. Time for a week off methinks!!
Creating a table in code isn't too bad, you just do a CREATE TABLE sql statment that names the table and describes the fields, which you could execute after a DROP TABLE statement. But I think that your approach would definitely be the better one in this situation.

Mike

Mike
EchoVue.com