Numbering of duplicates

[kamil.jedrzejewski]

Hi.
I need to do numbering of duplicate values and assign these numbers to
a table.
Example:
Table:
Field1
1
2
3
2
3
2
4
1

Reult I need:
Field1::Field2
1::1
2::1
3::1
2::2
3::2
2::3
4::1
1::2

where "::" is a field separator.
Additional assumptions:
- Field1 is a text field
- Updating is necessary (new data will appear in Field1 and should
also be numbered)

Thanks for help
Kamil

 

[Pieter Wijnen]

Something like
SELECT (SELECT COUNT(*)+1 FROM P1 WHERE P1.ID < P.ID AND P1.FIELD1=P.FIELD1)
AS ROWNUM, P.*
FROM P

HtH

Pieter

 

[kamil.jedrzejewski]

Something like
SELECT (SELECT COUNT(*)+1 FROM P1 WHERE P1.ID < P.ID AND P1.FIELD1=P.FIELD1)
AS ROWNUM, P.*
FROM P

HtH

Pieter









- Poka cytowany tekst -

It works fine, thanks.
Now I want to assign these values to a field in table.
I tried something like
UPDATE MyTable
SET CounterField="..."
WHERE CounterField is Null

but it's not working. "..." is your SELECT statement.
What I'm doing wrong?

Regards,
Kamil

 

[Pieter Wijnen]

I doubt that you can write a Query that will allow you to do this
you must probably create a Sub or Function that updates the records

something in the order of

Function updateCounter() as boolean

Dim db As DAO.database
Dim rsT as dao.recordset
Dim rsc as dao.recordset

set db = Access.CurrentDb
Set RsT = Db.Openrecordset("SELECT * FROM MyTable Where Counterfield is
null", DAO.dbOpenDynaset)

Set RsC = Db.OpenRecordset("TheAmmendedSQL"), DAO.dbopenSnapshot)

While Not rsT.EOF
rst.Edit
rsc.findfirst "ID=" & rst.Fields("id").Value
rst.Fields("CounterField").value = rsc.fields("RowNum").value
rst.update
rst.movenext
wend
'...

HtH

Pieter