How to compose a query to group numerous occurances of names see below for details

[Fergal Doyle]

I have a table with a list of names, and I want to compose a query that will sort the names and then number each name starting with the first occurrence of a particular name, I also want the number sequence to start at 1 for the next name. Basically for duplicate records I have to start the sequence numbers. Below is an example of what I’m trying to do.

Table
Name
AAA
AAA
CCC
AAA
CCC
BBB
BBB
AAA
BBB

Query
Name Number
AAA 1
AAA 2
AAA 3
AAA 4
BBB 1
BBB 2
BBB 3
CCC 1
CCC 2

[nico5038]

You can get the sequence number by using the DCOUNT() like:

SELECT Name, dcount("*","tblYours","Name<='" & [Name] & "'") as Sequence FROM tblYours;

Nic;o)

[Mariostg]

Rough but this should get you started. You can see the output in the debug window. All you have to do next is insert into a table.

Function runningSum()
    Dim rs As New ADODB.Recordset
    Dim cn As New ADODB.Connection
    Dim sql As String
    Dim i, j As Integer
    
    Set cn = CurrentProject.Connection
    
    sql = "SELECT COUNT(*) as c FROM Table1"
    rs.Open sql, cn, adOpenStatic
    j = rs!c
    ReDim field2numerize(j)
    ReDim fieldRunningSum(j)

    rs.Close
    
    sql = "SELECT field1, count(field1) as nField1" & _
        " FROM table1" & _
        " Group by field1"
        
    rs.Open sql, cn, adOpenStatic
    j = 1
    Do While Not rs.BOF And Not rs.EOF
        For i = 1 To rs!nField1
            field2numerize(j) = rs!field1
            fieldRunningSum(j) = i
            j = j + 1
        Next i
        rs.MoveNext
    Loop
    For i = 1 To UBound(field2numerize)
        Debug.Print field2numerize(i) & "   " & fieldRunningSum(i)
    Next i
End Function

[NeoPa]

This is not something generally supported by RDBMSs. If the data is exactly as you say, with nothing else to sort on, then your best hope is for a randomly assigned value, as there is nothing with which to determine the expected order, and hence the correct value.

If the data is determinable (IE. there is something else to sort on which determines the correct order) then Nico's approach will work perfectly for you.

[Fergal Doyle]

Thank you for reply.
I should have explained than I'm a novice when it comes to access so I couldn't get the solutions to work. I’ve tried to edit the query in design view; my knowledge of sql is very limited. When I entered the solution into the query I get a syntax error. I'm using access 2002.
I should have mentioned that there is a date when the name was entered into the table. So far all I could manage is below.
Field Name Date Name
Table Table1 Table1 Table1
Total Group By Group By Count
Sort Ascending

Result of Query

Table1 Query1
Name Date CountOfName
AAA 01/01/2010 1
BBB 01/01/2010 1
BBB 05/01/2010 1
AAA 20/01/2010 1
CCC 01/02/2010 1
AAA 02/02/2010 1
CCC 02/02/2010 1
AAA 14/02/2010 1
CCC 16/02/2010 1

[nico5038]

Just create a "plain" (no group by) query wih the field [Name] selected and open the SQL mode. There add a column in the SELECT like:

SELECT Name, dcount("*","Table1","Name<='" & [Name] & "'") FROM Table1

When this is working, add the other columns.

Nic;o)