Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Consecutive Numbers in a Table

Posted on 1998-12-10
5
Medium Priority
?
1,528 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 60 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

715 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