Create sequence number for table

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.
  
0
Utf
3/27/2010 2:55:01 AM
access 16762 articles. 3 followers. Follow

6 Replies
4940 Views

Similar Articles

[PageSpeed] 16

"Phillip" <Phillip@discussions.microsoft.com> wrote in message 
news:432342F6-AD55-405B-8CDF-43414FBE421D@microsoft.com...
> 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 



0
Larry
3/27/2010 3:10:03 AM
On Fri, 26 Mar 2010 19:55:01 -0700, Phillip
<Phillip@discussions.microsoft.com> wrote:

>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 [MVP]
0
John
3/27/2010 3:11:03 AM
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.........
"John W. Vinson" wrote:

> On Fri, 26 Mar 2010 19:55:01 -0700, Phillip
> <Phillip@discussions.microsoft.com> wrote:
> 
> >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 [MVP]
> .
> 
0
Utf
3/27/2010 4:19:01 AM
On Fri, 26 Mar 2010 21:19:01 -0700, Phillip
<Phillip@discussions.microsoft.com> wrote:

>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 W. Vinson [MVP]
0
John
3/27/2010 5:09:40 AM
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

Phillip wrote:
> 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.
>   
0
John
3/27/2010 12:11:38 PM
On Fri, 26 Mar 2010 23:09:40 -0600, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:

>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
 
0
Armen
3/28/2010 6:15:33 PM
Reply:

Similar Artilces:

Displaying report filters on Excel Pivot Tables
I am using pivot charts to display different departments' data, over the course of the year. When looking at the graph, I often filter it to see just one department. When viewing and printing, I would like to be able to see which department the chart is filtered on. Is there a way to do this? ...

How do you match one column's numbers with the 2nd column?
How do you math one column's numbers with the 2nd column? Like if you had: 1 2 2 3 3 6 4 5 6 7 ... and you wanted it sorted like so 2 2 3 3 6 6 1 4 5 7 Any idea how to do that? Thanks, Lee:confused -- lnsykalsk ----------------------------------------------------------------------- lnsykalski's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3403 View this thread: http://www.excelforum.com/showthread.php?threadid=53792 Assuming your data are in columns A and B insert a staging column before column B, with following formu...

email created in publisher has jibberish
When I create an email in Publisher and send it to my smtp email address, I open it up to read the email and it has the following jibberish before the actual email: v\:* {behavior:url(#default#VML);} o\:* {behavior:url(#default#VML);} b\:* {behavior:url(#default#VML);} .shape {behavior:url(#default#VML);} Is this something that can be prevented in the creation? Or is this something that happens after it is emailed? ...

Creating a frame window for MDI in a DLL
Have a question on creating an MDI 'application' from a DLL. I would like to launch an MDI 'psuedo' application from a DLL. That is, when this method is called from the main application, it would launch what would look like another application, that is an MDI frame with doc/view, etc.. I have seen something like this in .NET forms, however, I am using unmanged C++ code. I have some experimental code work done now and it appears that the call to LoadFrame fails (yes, I have included all of the necessary IDR_MAINFRAME resources). Is this possible? Has anyone done this? An...

Pivot table add one column
How can I add one column in pivot table? When I added a column of Average 100, pivot table created 3 more columns for the first 3 columns( and I don't like it) My data Book Label Price Average A Hard paper 200 100 B Soft 100 100 C Hard paper 200 100 F Plastic 50 100 K Soft 100 100 L Plastic 50 100 M Hard paper 200 100 K soft 100 100 R Soft 100 100 T Plastic 50 100 ------------------------------------------------------------------------------------------- My pivot ...

show items with no data option in pivot tables
Can anyone help with some bizarre results I'm experiencing with the show items with no values check box in the field settings menu for pivot tables. It appears to be showing field headers that don't exist in my data???? I'm using Excel 2000. Debra Dalgleish has some techniques at: http://www.contextures.com/xlPivot04.html In fact, she has an addin that you may like: http://www.contextures.com/xlPivotAddIn02.html AHuntington wrote: > > Can anyone help with some bizarre results I'm experiencing with the show > items with no values check box in the field setting...

Exchange 2007
Hi there, I'm trying to create a public contact folder in Exchange 2007. I can create a new public folder using the "New-PublicFolder" shell command, but it defaults to Post item types. Could anyone tell me how to set it so that it defaults to Contact item types? Thanks, -Richard rk <rk@leapbeyond.com> wrote: > Hi there, > > I'm trying to create a public contact folder in Exchange 2007. > > I can create a new public folder using the "New-PublicFolder" shell > command, but it defaults to Post item types. Could anyone tell me how > t...

Create Organization failed
I've moved my deployment to the production server and now want to create a new organization for testing purposes on the developmente server. When I run the New Organization wizard i get this error: Error| Create new Organization (Name=BigSky, Id=3a238cdc-773f-de11-81d3-000d9d4d28a8) failed with Exception: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.DirectoryServices.DirectoryServicesCOMException (0x80072037): There is a naming violation. (Exception from HRESULT: 0x80072037) I have had the same problem and sam...

Were can I find a design master I created?
I created a master slide design and it does not show up in the design template menu. Where could it be found? Julio Which version of PPT are you using? -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2007? http://www.echosvoice.com/2007.htm Fixing PowerPoint Annoyances http://tinyurl.com/36grcd PowerPoint 2007 Complete Makeover Kit http://tinyurl.com/32a7nx "Julio" <Julio@discussions.microsoft.com> wrote in message news:E246AB3D-9D8E-4C3B-A513-3E12EC97571E@microsoft.com... >I created a master slide design and it does not show up i...

BIN2DEC conversion for large binary numbers
Hello All, I need your help, i want to convert large binary numbers using excel but whenever i use the BIN2DEC function the result is negative which is not correct. Ex. BIN2DEC(1100110110) the result is -202 but if i use the calculator the result is 822 which is the correct value Any ideas? what shall i do to have the correct value? Thanks in advance A.M. Hi, Well I think it's 822 and my calculator confirms that and like you I get -202. Someone will no doubt explain why but in the meantime use this =SUMPRODUCT(MID("0"&A1,ROW(INDIRECT("1:"&a...

Help Creating A Rule
I need help creating a particular rule. I get many SPAM emails that contain no text at all. Instead it is a graphic type that displays text. It looks like "Plain Text" but it is really a photo embedded into an email. How can I write a rule that will prevent this type of SPAM. You can't search the body for text. It is worthless trying to write something that can spot the issue in the header because it can vary so much. Any way to write a rule that can detect the absent of all text? Don't use rules - set your Outlook Junk Filter to highest. --� Milly Staples [MVP - Ou...

Can a form linked to an autoLookup query update another table
I created a form that is updated with an AutoLookup query from another table once a certain linked field is filled in. Can this form be used to add a record to the source table with the updated information? When that record is saved, the record is added to the form's source table - assuming the form is bound to the source table. How does your form save the record? Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "oterosuz" <oterosuz@discussions.microsoft.com> wrote in message news:3608D905-8769-435E-B5E5-7B0F566AFD63@microsoft.com......

I have 2 Access DB with linked tables
In DB 1 is frmTokenDelivery and in DB 2 are tblImageBuild and tblHardwareRequest. I link to the tables from DB1. The DBs belongs to two seperate departments. The two departments perform seperate operations that must be completed before hardware is delivered to the user or to another dept. for further action. My Need: I want to display a date in DB 2 on frmTokenDelivery in DB 1. Field in DB 2 is ProDate (i.e projected dated). Sorry for the long explaination. Any assistance will be highly appreciated! With regards, Sokan33 wrote: > In DB 1 is frmTokenDelivery and in DB 2 are tblIm...

REPLY TO: how can i creat options in a cell of excel worksheet
You really need to give more information on what you're looking for. What do you mean by "Options"? Also, if you don't leave a message in the message portion when you're posting, many of us can't reply to the request. HTH, Barb Reinhardt ...

formatting numbers in excel 2003
In Excel 2000 I could set my format to number, 2 decimals, place comma, and fixed decimals would be checked in the tools, options. When I enter 329 and press enter I would get 3.29 in cell When I enter 329. and press enter I would get 329.00 in cell. I cannot make this work in 2003 If I have it set as describted above I get 3.29 in either case. Please tell me what to do. 20 years of entering this way will not change overnight if I cannot find the problem. Any help is greatly appreciated. tammie This appears to be an inadvertant change in XL2003. See this thread from a google sear...

Display Data Table in chart but don't show plot area
I am using Excel 2003. I have created some charts/graphs with data tables, chart titles, etc. My customer only wants to see the data table and chart title for each graph. Is there a way to turn off the plot area and change the size of the chart so that the chart title is close to the data table? But the data table merely reflects what is in the cells used to make the chart! Why not just display that data in a new format? Or am I missing something? best wished -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "RW" <RW@discussi...

calculated column in pivot table
Can I create a calculated column in a pivot table from two other columns. I have tried creating formulas but that does not seem to do the trick. The answer is yes. For anything less general, we'll need specifics on what you tried, and what happened. "does not seem to do the trick" does not give us much to go on. Regards, Fred "freeriderxlt" <st.jdaich@gmail.com> wrote in message news:97a60a38-262d-4a91-9474-7c26d8c44be3@a16g2000pre.googlegroups.com... > Can I create a calculated column in a pivot table from two other > columns. I have trie...

Autofill formulas when inserting rows in excel tables
Is it possible to have the formula in a column automatically be placed in a cell when you add a new row into an excel table (not a data table). Seems I saw this somewhere. ...

Seperate text and number to different cells
Hi, I have some data in cell (A1) which is alpha numeric, from this data I want number and text in seperate cells (B1) and (C1) respectively per the below example. A B C Raw Data Numbers Text asho344555k123 344555123 ashok 123ab47 12347 ab 1affu123 1123 affu Thanks Afroz The following User Defined Function will return the numerals in a mixed cell: Public Function ReturnNumerals(rng As Range) As String Dim sStr As String, i As Long, ...

Error creating new company GP9
I try and create a new company, it gets through most of the steps and then near the end gets a dexterity runtime error, I'm on the install server I can see the SQL file folder in the GP directory, and I am logged in as the administrator. Not sure what else to do here? Anyone had a smililar problem? ...

How do i compare numbers in cells with formula
I have a report I am attempting to automate as much as possible. What I have done is put in the following: Cell J is a current % Cell K is my product type (a number 19,20) Cell L is =if(k2=19,"65%",if(k2=20,"75%")) Cell M I want it to tell me whether the % in cell J is more then L, when I input the formula =if(j2>=L2,"Yes","No") it does not change but if I delete the formula in L and type a % in it works....HELP Ditch the double quotees around the percentages =if(k2=19,65%,if(k2=20,75%)) "nolechik" wrote: > I have a report I am a...

Numbers and letters...
Hi. I have in column A the numbers 1- 26, in column B the letters A- Z. I'm trying to create a formula wherein, when I type a number 1- 26 in C, the corresponding letter will appear in D (if I type '1', 'A' will appear, if I type '2', 'B' will appear, and so on). I was nesting a VLOOKUP in an IF function but I'm having problems. I suspect I'm making this more complicated than need be. I'd appreciate some assistance. silas On Nov 9, 12:09 pm, "silas" <silas.NOSPAMprop...@gmail.com> wrote: > Hi. I have in column A the n...

Extract multiple numbers from a cell
Here are some examples of text with multiple numbers embedded: A1 = 3031 // 2841;1886-ring road location A2 = 3305 //1455-historical A3 = //3491;3492 A4 = //inactive location; historical = 1790; enter new locaion Result should be: B1 = 3031;2841;1886 B2 = 3305;1455 B3 = 3491;3492 B4 = 1790 Try this UDF (User Defined function). From workbook launch VBE using Alt+F11. From menu Insert a Module and paste the below function.Close and get back to workbook and try the below formula. =ExtractNumbers(A1) Function ExtractNumbers(strData As String) As String Dim intTemp As Intege...

creating text file without delimiters
Does anyone know how to create a text file without delimiters from an excel doc. I need to create this file to use as import to another application. I would really like to just save it without the delimiters. For example, there are three columns in excel doc. col 1 col 2 col 3 52 100 13321 I want to save the data to look like 5210013321 without any spaces, commas or other delimiters. Thanks in advance One way: Public Sub NoDelimiterSV() Dim myRecord As Range Dim myField As Range Dim nFileNum As Long Dim sOu...

How do I find what table a field name from a form is in?
I've looked in properties in design view on the form and can't seem to find out what table the field is linked to. Any help would be appreciated. A table can be the form's Record Source. A control such as text box can have a Control Source from the Record Source table. If you look at a text box properties and see that it has a Control Source from a table, the Form's Record Source property will tell you which table that is. A query may include several tables, so if the Record Source is a query you may need to open the query in design view to discover the table or calc...