• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

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.
0
lancegallagher_expertsexchange
Asked:
lancegallagher_expertsexchange
1 Solution
 
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
 
thenelsonCommented:
make this field in your query
NextNumber: DCount("[AnyFieldNameInQuery]","YourQueyName","[AnyFieldNameInQuery] <=" & [AnyFieldNameInQuery])
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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
 
Mike EghtebasDatabase 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
 
[ 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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now