How do I to add sequence number within groups?

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.
lancegallagher_expertsexchangeAsked:
Who is Participating?
 
Mike EghtebasConnect With a Mentor Database and Application DeveloperCommented:
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
 
Stephen_PerrettCommented:
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
 
Simon BallCommented:
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
thenelsonCommented:
make this field in your query
NextNumber: DCount("[AnyFieldNameInQuery]","YourQueyName","[AnyFieldNameInQuery] <=" & [AnyFieldNameInQuery])
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
In the recordsource query of your form or report add a new field with alias name SN (stands for Sequence Number) where:

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
 
Mike EghtebasDatabase and Application DeveloperCommented:
Note:  above code need one more if statement, I am working on it.  But, you go ahead to as much shown above.

mike
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
[ fanpages ]IT Services ConsultantCommented:
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
 
[ fanpages ]IT Services ConsultantCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.