Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 6th, 2005, 08:46 AM
Authorized User
 
Join Date: Jan 2005
Location: , , .
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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

Reply With Quote
  #2 (permalink)  
Old March 6th, 2005, 06:29 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old March 6th, 2005, 06:43 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #4 (permalink)  
Old March 6th, 2005, 11:51 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

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
Reply With Quote
  #5 (permalink)  
Old March 6th, 2005, 11:59 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #6 (permalink)  
Old March 7th, 2005, 02:13 AM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Access sequential numbering in a query ChadKempel Access 3 June 4th, 2007 12:49 PM
Sequential numbering help gear1 Access 1 April 9th, 2007 06:31 AM
Sequential Numbers ricespn SQL Server 2000 14 November 1st, 2006 12:08 PM
Create Sequential Numbering That Does not Skip mrslockdown2003 Access 4 May 11th, 2005 11:45 AM
Sequential numbering a non auto number field dbartelt Access 3 May 5th, 2005 02:56 PM



All times are GMT -4. The time now is 06:02 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.