Add incremental number in sql for specific values

[jaryszek]

hi guys,

i have table like here:

ID	Name	Function
1	Topo1	data
2	Topo1	data
3	Topo1	data
4	Topo1	Diff
5	Topo2	Some1

and what i want to get for specific Name,in this example for Name = "Topo1" and Function = "data" i result like here in Query:

ID	Name	Function	IncrementalNumber
1	Topo1	data	1
2	Topo1	data	2
3	Topo1	data	3
4	Topo1	Diff	1
5	Topo2	Some1	1

so i added IncrementalNumnber column and thinking how to do it in Access SQL ?
Maybe Dcount?
(and if dcount will work in Excel adodb recordset after?)

Best,
Jacek

[CJ_London]

this is one way to do it

SELECT TopoList.ID, TopoList.Name, TopoList.Function, DCount("*","topolist","ID<=" & [ID] & " AND [Function]='" & [function] & "' AND [Name]='" & [Name] & "'") AS Expr1
FROM TopoList
ORDER BY TopoList.ID;

Note that Name and Function are both reserved words and should not be used as field names

don't know about ADO, you will have to try it

[jaryszek]

hi,

thank you.
can you please explain how this is working? Especially criteria for DcOunt?

Best,
Jacek

[CJ_London]

with your example data dcount would still work if the name was excluded from the criteria, but you said 'this example for Name = "Topo1" and Function = "data" '
which is what you see in the criteria

[Function]='" & [function] & "' AND [Name]='" & [Name] & "'"

Then you only want to count the ID's that are the same or less than the ID for each record so we have "ID<=" & [ID]
google dcount to find out more

If topo1....data appears in a later record (e.g. ID 10), it will have a count of 4.

If your real data does not match your example, then you will have a problem and would no doubt need a completely different solution

[jaryszek]

thank you !!

Jacek