 |
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|

March 6th, 2005, 08:46 AM
|
Authorized User
|
|
Join Date: Jan 2005
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Sequential Row numbering in queriws
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
|

March 6th, 2005, 06:29 PM
|
Friend of Wrox
|
|
Join Date: Oct 2004
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
|
|
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
|

March 6th, 2005, 06:43 PM
|
Friend of Wrox
|
|
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
|
|
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
|

March 6th, 2005, 11:51 PM
|
Friend of Wrox
|
|
Join Date: Oct 2004
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
|
|
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
|

March 6th, 2005, 11:59 PM
|
Friend of Wrox
|
|
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
|
|
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
|

March 7th, 2005, 02:13 AM
|
Friend of Wrox
|
|
Join Date: Oct 2004
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
|
|
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
|
|
 |