Solved

How do I to add sequence number within groups?

Posted on 2005-05-15
258 Views
Hi all, I'm having a problem figuring this out and it's driving me crazy. Hopefully someone's come across it before. I've got a column of ID Numbers and I need to find out a sequential number within the group and update the field. e.g

ID            Sequence Number
----------   --------
C238         1
C238         2
P100         1
P100         2
P100         3
P100         4
P101         1
P102         1
P102         2
P102         3

Thanks, Lance.
0

LVL 11

Expert Comment

What is Sequence Number used for?

If  the Sequence Number field is used merely to order information as it is entered into the system I would use an autonumber field The numbers would be different but the sort order would be the same.

Steve
0

LVL 15

Expert Comment

so is this applying a sequence number for each unique ID?

SP's above would be the easiest way to do it, but it sounds like you specifically want to have the sequence based on the repeated records in ID.

if this is the case its an interesting puzzle.

if you do a group by query grouping on id, and counting id in a seperate column, this will give you the max sequence number for ID.

the problem will be iterating through the data for each id.  is there any other data in the table which could be used to distinguish the individual records for each ID?
0

LVL 39

Expert Comment

make this field in your query
NextNumber: DCount("[AnyFieldNameInQuery]","YourQueyName","[AnyFieldNameInQuery] <=" & [AnyFieldNameInQuery])
0

LVL 33

Expert Comment

SN:fnSN([ID])

Important: Make sure this query is sorted by [ID] Asce.

In a standard module, under module tab, paste:

Public ID_var As String
Public SNvar as intger

Public Function fnSN(IDvar as String) As Integer
If ID_var = "" or ID_var < IDvar Then
SNvar =1
Else
SNvar =1+SNvar
End If
ID_var =IDvar
fnSN=SNvar
End Function

mike
0

LVL 33

Expert Comment

Note:  above code need one more if statement, I am working on it.  But, you go ahead to as much shown above.

mike
0

LVL 33

Expert Comment

Assumption: All IDs preceed by a letter followed by some number.  And, the triling number is three digits only.

If this is a true statement, then following code would work.  Or, if the trailing number may follow more than on letter instead, code below could be adjusted for it.  The same applies if more than 3-digit trailing number follows the single letter.

But for now, we will be dealing one letter followed by a 3-digit number

-----------
Public ID_var As Integer
Public SNvar as Intger

Public Function fnSN(IDvar as String) As Integer

Dim intID as Integer

intID =int(Mid(IDvar,2))

If ID_var = "" or ID_var > intID Then
SNvar =1
Else
SNvar =1+SNvar
End If
ID_var =intID
fnSN=SNvar
End Function
0

LVL 33

Accepted Solution

Sorry... use

Public ID_var As Integer
Public SNvar as Intger

Public Function fnSN(IDvar as String) As Integer

Dim intID as Integer

intID =int(Mid(IDvar,2))

If ID_var = 0 or ID_var > intID Then      '<-- ID_var is changed to Integer (was string)
SNvar =1
Else
SNvar =1+SNvar
End If
ID_var =intID
fnSN=SNvar
End Function
0

LVL 35

Expert Comment

Hi Lance,

Let's assume the table where you have stored the IDs is called [Table1].

1. Create a new table, [Factor], that contains a single column, [Number], and populate as follows:

Factor : Table

Number
1
2
3
4
(add extra rows depending on the maximum count of the same [ID] in [Table1], e.g. if any ID occurs 10 times, add records for 5, 6, 7, 8, 9 & 10)

2. Create a new query, [Total_IDs] as follows:

SELECT
[Table1].[ID],
Count([Table1].[ID]) AS [Total_Count]
FROM
[Table1]
GROUP BY
[Table1].[ID]
ORDER BY
[Table1].[ID]

3. Create a new query, [List_IDs] as follows:

SELECT
[Table1].[ID],
[Factor].[Number]
FROM
[Factor],
[Table1]
INNER JOIN [Total_IDs] ON [Table1].[ID] = [Total_IDs].[ID]
GROUP BY
[Table1.ID],
[Factor].[Number],
[Total_IDs].[Total_Count]
HAVING
[Factor].[Number]<=[Total_IDs].[Total_Count]

Open the "List_IDs" query to display the results you require.

BFN,

fp.

0

LVL 35

Expert Comment

Oh sorry...

point 3 to be exactly as your question results:

"List_IDs" is defined as:

SELECT
[Table1].[ID],
[Factor].[Number] As [Sequence Number]
FROM
[Factor],
[Table1]
INNER JOIN [Total_IDs] ON [Table1].[ID] = [Total_IDs].[ID]
GROUP BY
[Table1].[ID],
[Factor].[Number],
[Total_IDs].[Total_Count]
HAVING
[Factor].[Number]<=[Total_IDs].[Total_Count]

This produces:

ID      Sequence Number
C238      1
C238      2
P100      1
P100      2
P100      3
P100      4
P101      1
P102      1
P102      2
P102      3

BFN,

fp.
0