Solved

Consecutive Numbers in a Table

Posted on 1998-12-10
5
1,511 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

743 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

11 Experts available now in Live!

Get 1:1 Help Now