Solved

Consecutive Numbers in a Table

Posted on 1998-12-10
5
1,516 Views
Last Modified: 2008-01-09
How do I get consecutive numbers into a table based on how many records are imputed.  Example of consecutive numbers: 1, 2, 3, 4, 5, 6, etc.  

I'm pulling data from another access database and imputing it in a table.  The table has a number associated with each entry.  This is done automatically when you enter a record from a form.  When I'm imorting information I want to be able to give it a beginning number and have add a consecutive number for each transaction that is entered.

I tried using the autonumber but I want to be able to enter a beginning number instead of having it assign one for me.

I do not have a clue how to even start this so any help would be appreciated.

Jay
0
Comment
Question by:jaymrk
  • 2
  • 2
5 Comments
 
LVL 10

Expert Comment

by:brewdog
ID: 1970582
Jay:

How are you inputting the data into the table? With a flat import, or with a recordset loop, or an SQL string, or . . . ?

If you are doing a straight import, you could do something like this after the import:

dim intStart as integer
dim rs as recordset

set rs = currentdb().openrecordset("YourTable")
intStart = 1         'or whatever value you want to start with

rs.movefirst
do until rs.nomatch
   rs.findnext "NumberingField = Null"
   rs.Edit
   rs("NumberingField") = intStart
   rs.Update
Loop

Does that give you a lead? Is that the kind of thing you're looking for?

brewdog
0
 

Author Comment

by:jaymrk
ID: 1970583
I'm bringing it into my database as a linked table.  I was hoping to be able to do something through an append query, I'm not really an expert so I was hoping for something simple.  Can you give me a lead on something aside from an import?


Jay
0
 
LVL 3

Accepted Solution

by:
OmegaJunior earned 20 total points
ID: 1970584
Greetings, jaymrk,

You could try this angle:
When a table has got an autonumber, it remembers the highest number even when it contains no records. Therefore a new record will be assigned the highest number plus one. To change the first number (and thereby all consecutive numbers) in such a table, there are two options.
ONE
For instance: you want your first record to begin with the number 8. Create the table needed, and insert seven dummy records. Delete them. Now, the next record added will be assigned number 8. Thus when using a adding query you will get what you asked for.
TWO
For instance: you want your first record to begin with the number 1, but right now it has got the number 364. Duplicate the table needed, then either paste the records from the original to the duplicate table or use a adding query.

I believe when using most other techniques than those described above, one is more likely to be obliged to use VBA code.

Imagine!
0
 

Author Comment

by:jaymrk
ID: 1970585
Thanks for the help!  

I used two queries, one with the autonumber and the second one I did a calculation using the minimum autonumber minus the original autonumber plus one.  This gave me a consecutive number of 1 through the number of records.  

I don't know if the above explanation made since but it worked, again thanks.

Jay
0
 
LVL 3

Expert Comment

by:OmegaJunior
ID: 1970586
Greetings Jay,

Now you confused me with the comment to my answer. You subtracted the original autonumber from the minimum autonumber and added one. That gave you a consecutive number of 1 through the number of records. Isn't the minimum autonumber always 1? Or did you mean the minimum that lives in your table? Because if you subtracted any number from 1 other than 0, your autonumber would end up below zero. This should raise an Access error.

You're welcome

Imagine!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now