jaymrk
asked on
Consecutive Numbers in a Table
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
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
ASKER
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
Jay
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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!
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!
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(
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