Solved

Consecutive Numbers in a Table

Posted on 1998-12-10
5
1,523 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

710 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