Automatic Numbering Titles

[Jorge mst]

Hello

I have MS Access 2007.
In a table I have the ID and the numberOfTitle
In a consult I have the ID and Title.
In numberOfTitle I say the number of the Title (similar to formating titles
in MS Word).

I need to find a expression SQL that calculate the Title, like in this
example:

ID / numberOfTitle / Title
1 1 1
2 2 1.1
3 3 1.1.1
4 3 1.1.2
5 3 1.1.3
6 2 1.2
7 3 1.2.1
8 1 2
9 2 2.1
10 2 2.2
11 3 2.2.1
12 3 2.2.2

Tanks in advance

 

[Jeanette Cunningham]

Hi Jorge,
something like this:
If you are not using this in a form,
create the query in the access query designer.
Put both tables in the query designer.
Create a join on ID
Use the criteria row for the 'numberOfTitle', for example put in 2.
Switch the query to datasheet view and you will see the Title for
numberOfTitle = 2 and those where ID = 2.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Lord Kelvan]

use this expression

numberOfTitle: Len(replace([title],".",""))

what it does is strips the .'s and then returns the length

if you want to be on the safe side use

Len(replace(trim([title],".","")))

hope this helps

Regards
Kelvan