Solved

Consecutive Numbers in a Table

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access 2016 - Labels prompt to print 4 31
Create a EXCEL FIle from Access Procedure 1 14
FrontEnd tools to create web database application 7 56
Access Update Query 1 20
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

776 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