Auto Number Function

[Guest]

Hi folks,

Does anyone know how to create a function which can be used in query to auto
number the record? Some thing like:

function autonumber(start number as interger) as interger
--
--
--
end function.

Thanks in advance.

Tim.

 

[David C. Holley]

What are you trying to accomplish? Access has the capability of
designating fields in a table that are incremented automatically when a
new record is added. Why do you need an autonumber?

 

[Guest]

I need to use it in my query not in table.

Thanks.

Tim.

 

[Guest]

Here's a roundabout way:

Split it into three queries:
1. Make one query an append query to a table that has an autonumber field.
Run your original query once as a make-table query to create it the first
time, then change that query to an append to this table.
2. Have a delete query to flush the contents of this table before you run
the append query.
3. Make your third (select) query run against this table. Add another field
that is like this:

NewID: [KeyID]-DMin("[KeyID]","[ThisTable]")+1

Not exacly an efficient way, but here is what would happen:

1. The delete query flushes all records.
2. The append query appends all record, assigning each an autonumber.
3. The select query uses the autonumber of each record less the first
autonumber found plus 1 to give you a sequence starting with 1.

Someone else probably has an easier way. Just an idea.

 

[David C. Holley]

Why do you need a autonumber in the query?

I need to use it in my query not in table.

Thanks.

Tim.

:

 

[Rick Brandt]

Why do you need a autonumber in the query?

Amazing how often we see that question isn't it? In 10-plus years of working
with Access I have never encountered a need to sequentially number the output of
a query and yet that question is asked almost daily in these groups.

 

[David C. Holley]

I did think about the other (my favorite) 'What are you trying to
accomplish?' -or- 'What is the business process that you're trying to
automate?'

 

[Guest]

use something like this:
Module:
Dim AutoNumID As Long

Sub InitAutoNum()
AutoNumID = 0
End Sub

Public Function fnAutoNum(ByVal ID As Long) As Long
AutoNumID = AutoNumID + 1
fnAutoNum = AutoNumID
End Function

first call InitAutoNum
then run an make table like
SELECT Table.ID, fnAutoNum([ID]) AS AutoID INTO tmpTable FROM

;
try something like this.

 

[Guest]

I am suprised that no one mentioned the fact that autonumber fields will not
necessarily remain sequential. If you cancel adding record or delete a
record, then there are gaps. Actually, although it is not one of the Access
"Ten Commandments", it should be:

Thou shalt use Autonumber only for relating thy tables and only for relating
thy tables shall ye use Autonumber. Gazing upon the values of thy
autonumbers or in thy heart considering calculations upon thy autonumber will
lead thee into peril and misery.

use something like this:
Module:
Dim AutoNumID As Long

Sub InitAutoNum()
AutoNumID = 0
End Sub

Public Function fnAutoNum(ByVal ID As Long) As Long
AutoNumID = AutoNumID + 1
fnAutoNum = AutoNumID
End Function

first call InitAutoNum
then run an make table like
SELECT Table.ID, fnAutoNum([ID]) AS AutoID INTO tmpTable FROM

;
try something like this.

Hi folks,

Does anyone know how to create a function which can be used in query to auto
number the record? Some thing like:

function autonumber(start number as interger) as interger
--
--
--
end function.

Thanks in advance.

Tim.

 

[David C. Holley]

Thou shalt also keep thy Irish Pub or Taverna in sound financial health.

I am suprised that no one mentioned the fact that autonumber fields will not
necessarily remain sequential. If you cancel adding record or delete a
record, then there are gaps. Actually, although it is not one of the Access
"Ten Commandments", it should be:

Thou shalt use Autonumber only for relating thy tables and only for relating
thy tables shall ye use Autonumber. Gazing upon the values of thy
autonumbers or in thy heart considering calculations upon thy autonumber will
lead thee into peril and misery.

:

use something like this:
Module:
Dim AutoNumID As Long

Sub InitAutoNum()
AutoNumID = 0
End Sub

Public Function fnAutoNum(ByVal ID As Long) As Long
AutoNumID = AutoNumID + 1
fnAutoNum = AutoNumID
End Function

first call InitAutoNum
then run an make table like
SELECT Table.ID, fnAutoNum([ID]) AS AutoID INTO tmpTable FROM

;
try something like this.

:

Hi folks,

Does anyone know how to create a function which can be used in query to auto
number the record? Some thing like:

function autonumber(start number as interger) as interger
--
--
--
end function.

Thanks in advance.

Tim.

 

[Guest]

Take not the name of thy Irish Pub or Taverna in vain.

Thou shalt also keep thy Irish Pub or Taverna in sound financial health.

I am suprised that no one mentioned the fact that autonumber fields will not
necessarily remain sequential. If you cancel adding record or delete a
record, then there are gaps. Actually, although it is not one of the Access
"Ten Commandments", it should be:

Thou shalt use Autonumber only for relating thy tables and only for relating
thy tables shall ye use Autonumber. Gazing upon the values of thy
autonumbers or in thy heart considering calculations upon thy autonumber will
lead thee into peril and misery.

:

use something like this:
Module:
Dim AutoNumID As Long

Sub InitAutoNum()
AutoNumID = 0
End Sub

Public Function fnAutoNum(ByVal ID As Long) As Long
AutoNumID = AutoNumID + 1
fnAutoNum = AutoNumID
End Function

first call InitAutoNum
then run an make table like
SELECT Table.ID, fnAutoNum([ID]) AS AutoID INTO tmpTable FROM

;
try something like this.

:


Hi folks,

Does anyone know how to create a function which can be used in query to auto
number the record? Some thing like:

function autonumber(start number as interger) as interger
--
--
--
end function.

Thanks in advance.

Tim.

 

[David C. Holley]

Brother Klattu, me beliefths that thou are mistaken much (young padawan).
Take not the name of thy bartender in vain and tip heavily.

Take not the name of thy Irish Pub or Taverna in vain.

:

Thou shalt also keep thy Irish Pub or Taverna in sound financial health.

I am suprised that no one mentioned the fact that autonumber fields will not
necessarily remain sequential. If you cancel adding record or delete a
record, then there are gaps. Actually, although it is not one of the Access
"Ten Commandments", it should be:

Thou shalt use Autonumber only for relating thy tables and only for relating
thy tables shall ye use Autonumber. Gazing upon the values of thy
autonumbers or in thy heart considering calculations upon thy autonumber will
lead thee into peril and misery.

:



use something like this:
Module:
Dim AutoNumID As Long

Sub InitAutoNum()
AutoNumID = 0
End Sub

Public Function fnAutoNum(ByVal ID As Long) As Long
AutoNumID = AutoNumID + 1
fnAutoNum = AutoNumID
End Function

first call InitAutoNum
then run an make table like
SELECT Table.ID, fnAutoNum([ID]) AS AutoID INTO tmpTable FROM

;
try something like this.

:



Hi folks,

Does anyone know how to create a function which can be used in query to auto
number the record? Some thing like:

function autonumber(start number as interger) as interger
--
--
--
end function.

Thanks in advance.

Tim.

 

[Guest]

This brew must be good enough to make me hallucinate. I remember in 2000
(year and version) doing exactly that for about 100 users in 4 separate
locations, some of whom were still on Windows 98.

Using the word "young" and my name in the same post is really silly. Who do
you think gave Herman Hollierath the idea for the punch card?

Would you pass my peso to the bartender for me? I can't reach the bar while
I'm all tangled up in the foot rail down here.

Brother Klattu, me beliefths that thou are mistaken much (young padawan).
Take not the name of thy bartender in vain and tip heavily.

Take not the name of thy Irish Pub or Taverna in vain.

:

Thou shalt also keep thy Irish Pub or Taverna in sound financial health.

Klatuu wrote:

I am suprised that no one mentioned the fact that autonumber fields will not
necessarily remain sequential. If you cancel adding record or delete a
record, then there are gaps. Actually, although it is not one of the Access
"Ten Commandments", it should be:

Thou shalt use Autonumber only for relating thy tables and only for relating
thy tables shall ye use Autonumber. Gazing upon the values of thy
autonumbers or in thy heart considering calculations upon thy autonumber will
lead thee into peril and misery.

:



use something like this:
Module:
Dim AutoNumID As Long

Sub InitAutoNum()
AutoNumID = 0
End Sub

Public Function fnAutoNum(ByVal ID As Long) As Long
AutoNumID = AutoNumID + 1
fnAutoNum = AutoNumID
End Function

first call InitAutoNum
then run an make table like
SELECT Table.ID, fnAutoNum([ID]) AS AutoID INTO tmpTable FROM

;
try something like this.

:



Hi folks,

Does anyone know how to create a function which can be used in query to auto
number the record? Some thing like:

function autonumber(start number as interger) as interger
--
--
--
end function.

Thanks in advance.

Tim.

 

[Guest]

Hi Tim,

Check out Stephen Lebans Function Serialize at

http://www.lebans.com/rownumber.htm

I think this is what you were looking for...a way to number rows in a query
in sequence not in a table.

lwells

 

[Guest]

Being a newbie to the group, I'll just throw in my 2 cents here. I took 2
Access Training Classes (guess they didn't help much, eh?) and autonumbering
wasn't explained very well. Just the name, auto number, conjures up a fairy
tale world that Access will number the field for you (at least in my feeble
mind). I don't think I'm alone in my thinking here, otherwise this
wouldn't be asked so much. I understand it now, just seems that Access
could have called it something else that would be more self explanatory.

Linda

 

[Guest]

I tried using this from lebans site, but it won't open. I can get it to
unzip, then it tells me it is read open, I agree, and I get nothing but a
blank screen.

Linda

 

[David C. Holley]

What's a punch card? It that like a LP?

This brew must be good enough to make me hallucinate. I remember in 2000
(year and version) doing exactly that for about 100 users in 4 separate
locations, some of whom were still on Windows 98.

Using the word "young" and my name in the same post is really silly. Who do
you think gave Herman Hollierath the idea for the punch card?

Would you pass my peso to the bartender for me? I can't reach the bar while
I'm all tangled up in the foot rail down here.


:

Brother Klattu, me beliefths that thou are mistaken much (young padawan).
Take not the name of thy bartender in vain and tip heavily.

Take not the name of thy Irish Pub or Taverna in vain.

:



Thou shalt also keep thy Irish Pub or Taverna in sound financial health.

Klatuu wrote:


I am suprised that no one mentioned the fact that autonumber fields will not
necessarily remain sequential. If you cancel adding record or delete a
record, then there are gaps. Actually, although it is not one of the Access
"Ten Commandments", it should be:

Thou shalt use Autonumber only for relating thy tables and only for relating
thy tables shall ye use Autonumber. Gazing upon the values of thy
autonumbers or in thy heart considering calculations upon thy autonumber will
lead thee into peril and misery.

:




use something like this:
Module:
Dim AutoNumID As Long

Sub InitAutoNum()
AutoNumID = 0
End Sub

Public Function fnAutoNum(ByVal ID As Long) As Long
AutoNumID = AutoNumID + 1
fnAutoNum = AutoNumID
End Function

first call InitAutoNum
then run an make table like
SELECT Table.ID, fnAutoNum([ID]) AS AutoID INTO tmpTable FROM

;
try something like this.

:




Hi folks,

Does anyone know how to create a function which can be used in query to auto
number the record? Some thing like:

function autonumber(start number as interger) as interger
--
--
--
end function.

Thanks in advance.

Tim.

 

[David C. Holley]

Autonumber is just that a NUMBER that is AUTOmatically created. However,
it is only available at the TABLE level and then when it is setup as a
field property.