Create sequence number for table

[Phillip]

Hi,
I have a table with a Title field, comment field and a Sequence field. I
would like code to sort the table by Title and then fill in the Sequence
field based on the order of the records. The first record would have a
sequence number of 1, the second record a 2, etc.
Can someone help me with code to do this?
Thanks in advance.

 

[Larry Linson]

Hi,
I have a table with a Title field, comment field and a Sequence field. I
would like code to sort the table by Title and then fill in the Sequence
field based on the order of the records. The first record would have a
sequence number of 1, the second record a 2, etc.
Can someone help me with code to do this?
Thanks in advance.

The question is _why_ would you want to do this? You can sort the table by
Title whenever you want to use it. And, with what you are asking for, what
happens when you add a record that sorts between two existing titles?

Records in a relational table are, by definition, UNordered. The relational
way is that you order the records in the Query you use to retrieve them,
preferrably by some included natural value.

It would be possible to do what you want, but then all you could do with
that is to use it to order the records which you can already do, as you
stated, by sorting by title.

Larry Linson
Microsoft Office Access MVP

 

[John W. Vinson]

Hi,
I have a table with a Title field, comment field and a Sequence field. I
would like code to sort the table by Title and then fill in the Sequence
field based on the order of the records. The first record would have a
sequence number of 1, the second record a 2, etc.
Can someone help me with code to do this?
Thanks in advance.

It's a bad idea.

Suppose you have 3127 records and then add "Aardvark" as a new title.

Do you renumber 3125 records to make room for the new entry?

What if the sequence number is referenced in five other tables? Or 200 copies
of a printout? Or in a dozen people's memories? Do you erase them all to make
the change?

If you recognize that it's a bad idea, or you have good reason to believe that
your database is an exception, you can run an Update query. Create a query
based on the table, and update Sequence to

=DCount("*", "yourtable", "[Title] <= " & [Title])

This will count all the records with a title up to and including the current
record's title, and plunk that into the Sequence field.

Don't edit or add any titles after you do this, or you'll have to do it all
over again.

 

[Phillip]

FYI
I want to do this to print a report with one record per page and with page
numbers.
I can then also produce a table of contents from the table using the
sequence number as page numbers. Then I can also produce the 5 index tables
I need based on the other fields in the table. The index tables are very
easy to produce if the page numbers are in the table. Each time I want to
produce the report the sequence numbers would be updated for new or deleted
records and I can procude the table contents and index tables with the touch
of a button.
If there is a better way to do this I don't know how.........

Hi,
I have a table with a Title field, comment field and a Sequence field. I
would like code to sort the table by Title and then fill in the Sequence
field based on the order of the records. The first record would have a
sequence number of 1, the second record a 2, etc.
Can someone help me with code to do this?
Thanks in advance.

It's a bad idea.

Suppose you have 3127 records and then add "Aardvark" as a new title.

Do you renumber 3125 records to make room for the new entry?

What if the sequence number is referenced in five other tables? Or 200 copies
of a printout? Or in a dozen people's memories? Do you erase them all to make
the change?

If you recognize that it's a bad idea, or you have good reason to believe that
your database is an exception, you can run an Update query. Create a query
based on the table, and update Sequence to

=DCount("*", "yourtable", "[Title] <= " & [Title])

This will count all the records with a title up to and including the current
record's title, and plunk that into the Sequence field.

Don't edit or add any titles after you do this, or you'll have to do it all
over again.

 

[John W. Vinson]

If there is a better way to do this I don't know how.........

There is; on a Report you can put a textbox with a control source of =1, and
set its Running Sum property to Over All. The first textbox will show a 1, the
second 2, and so on ad infinitum; and it will recalculate next time you run
the report, even if the data changes.

 

[John Spencer]

Are the titles UNIQUE and is the TITLE field a text (not memo) field? Does
the table have a primary key?

You should be able to create a ranking column in a query to give you the
needed number based on the title and (if needed) the primary key. With the
ranking number you can generate the other documents you want.

Is the comment field a memo field? If so that makes the solution slightly
more complex. I will assume so.

Assuming UNIQUE titles the ranking query would look like:
SELECT A.Title, Count(B.Title) as RankNumber
FROM [YourTable] as A INNER JOIN [YourTable] as B
ON A.Title >= B.Title
GROUP BY A.Title

You could then join that to your table in another query to add in the comment
field (assuming it is a memo field).

SELECT A.Title, A.Comment, Q.Rank
FROM [YourTable] as A INNER JOIN qRankingQuery as Q
ON A.Title = Q.Title


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Armen Stein]

There is; on a Report you can put a textbox with a control source of =1, and
set its Running Sum property to Over All. The first textbox will show a 1, the
second 2, and so on ad infinitum; and it will recalculate next time you run
the report, even if the data changes.

But then you don't have the information for the table of contents.

In this case I suggest you load a work table (or tables) with
everything you need for the report - data records, sequences, TOC
entries, etc. I wouldn't update the sequence values in the main table
- too messy, plus it won't handle multiple users running the reports
concurrently.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com