numbering records in sequence

[Guest]

In a table or query can access do a number field that automatically updates
whenever a record is added or deleted? It seems that "auto number" type data
cannot be changed once it has been assigned to a particular record. I need a
field that keeps a running count of the total number of records as new ones
are added or some are deleted. A field that updates and keeps the numbers in
sequence. Am I missing the obvious? Thanks much for this forum.
Bob Levin

 

[Guest]

Short answer is No. You can do this with a query (SELECT Count() ) or domain
aggregate function (DCount() ).

 

[Joseph Meehan]

In a table or query can access do a number field that automatically
updates whenever a record is added or deleted? It seems that "auto
number" type data cannot be changed once it has been assigned to a
particular record. I need a field that keeps a running count of the
total number of records as new ones are added or some are deleted. A
field that updates and keeps the numbers in sequence. Am I missing
the obvious? Thanks much for this forum.
Bob Levin

To do a count, you can do that in a query.

To keep the records in an order, you can number them in order, take a
look at:

http://www.databasedev.co.uk/automatically_increment_value.html

For one example.

If you delete one, don't worry, if you sort on the number it will keep
them in order and use the query to count them.

 

[Allan Murphy]

Be careful, if you use this field as a link to other tables you should not
renumber as you will end up with incorrect data in your reports etc.

 

[Guest]

Thanks Joseph. I looked at the web page and got a bit confused. I am not
looking to have an ID number assigned to a row.
I want a field that is independant (not linked permanently to any
records). As a record is added, the numbers in this field would adjust to
keep the correct number of records and in the correct numerical sequence. Im
not sure if this is what you are trying to tell me.Thanks much for your
response.
I will try to follow what matarcallarse posted. It sounds like that
method would require me to run a new query each time I added a new record.
I'm sort of new at this so I'm not sure yet what I can come up with.
Thanks Bob L.

 

[Dirk Goldgar]

In a table or query can access do a number field that automatically
updates whenever a record is added or deleted? It seems that "auto
number" type data cannot be changed once it has been assigned to a
particular record. I need a field that keeps a running count of the
total number of records as new ones are added or some are deleted. A
field that updates and keeps the numbers in sequence. Am I missing
the obvious? Thanks much for this forum.

Record numbers aren't really meaningful, as they aren't related to the
data in the record. To keep your records in sequence by time of entry,
I suggest you add a date/time field to the table, and set it to Now() as
each record is added, or as its default value. Then you can sort by
this field to see your records in entry sequence.

 

[Guest]

If the query is a RecordSource of a report you can do it very simply in the
report; just add an unbound text box to the detail section with a
ControlSource of =1 and a RunningSum property of Over All.

To do it in a query the following is an example where the rows are ordered
by date and the YourDate field 's values are not unique where the YourID
column is the primary key:

SELECT YourDate,
(SELECT COUNT(*)
FROM YourTable AS T2
WHERE T2.YourDate <= T1.YourDate
AND ( T2.YourID <= T1.YourID
OR T2.YourDate <> T1.YourDate)) AS Counter
FROM YourTable AS T1
ORDER BY YourDate, YourID;

To order the rows by the non-sequential primary key values is even simpler:

SELECT YourDate,
(SELECT COUNT(*)
FROM YourTable AS T2
WHERE T2.YourID <= T1.YourID) AS Counter
FROM YourTable AS T1
ORDER BY YourID;

There is no point in storing the Counter values in the table and recomputing
them every time the table is updated; they can be computed on the fly as
above whenever necessary and returned in the result table of the query.

If you want to identify rows by order of entry a DateTimeStamp column with a
DefaultValue property of Now() is the most reliable and can be used in place
of the YourID column in the second query above to generate the Counter
values. An autonumber only guarantees uniqueness, not necessarily sequence.
You can, however, generate your own sequential 'pseudo-autonumber' values and
you'll find a demo of mine of how to do this in a multi-user environment at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23839&webtag=ws-msdevapps


Ken Sheridan
Stafford, England

 

[Guest]

Thanks Everyone for your help. I guess Iv'e gotten in a little over my head
here. I didn't realize it required advanced skills. I may have to continue
using The work-a-round method that I've been using.
I simply insert a new field and manually type in number 1 in the first
cell, then arrow down one cell and type the number 2. Then I hold the down
arrow key down and it will number the whole column(50 or 100 records) in
about a couple of seconds. Of course everytime I add a new record, I have to
delete that number column and repeat that process again.
I may have to take a course in syntax to understand all the symbols (

= ( ) : <>) or maybe I could get a more simple step by step explanation.

Thanks Much.
Bob Levin