Incrementally increase by 1 function that can be used in a query

  • Thread starter Thread starter HanSolo
  • Start date Start date
H

HanSolo

Could someone help me tweak my below function (that can be run via
query expression) so that it incrementally adds 1 to each row in a
given group?

I understand that the table autonumber feature accomplishes this, but
I'd like to do it via "function called by query expression" since I'll
need the flexibility to restart the incrementing based on groupings.

What I have below does not add incrementally, as such just adds 1 to
everything.

Public Function FIDAssigner(A)

intnumber = A

Select Case intnumber
Case 1
FIDAssigner = (A + 1)

Case 2
FIDAssigner = (A + 1)

Case 3
FIDAssigner = (A + 1)

Case 4
FIDAssigner = (A + 1)

End Select

End Function
 
Correction to my function, this should make more sense. "A"
concatenated separates the groupings:

Public Function FIDAssigner(A,B)


intnumber = B


Select Case intnumber
Case 1
FIDAssigner = A & (B + 1)


Case 2
FIDAssigner = A & (B + 1)


Case 3
FIDAssigner = A & (B + 1)


Case 4
FIDAssigner = A & (B + 1)


End Select


End Function
 
The general idea is to use a subquery that counts, for each record, the
number of records in the group before the current record (in
the current sort order, of course).

Here's an example that works in the Northwind sample database. I'll
leave it to you to apply it to your own structure. It returns the order
details, sorted by OrderID and ProductID, with a sequential number that
starts at 1 for each OrderID:

SELECT
(SELECT COUNT(C.OrderID)
FROM [Order Details] AS C
WHERE C.OrderID = A.OrderID
AND C.ProductID <= A.ProductID) AS Seq,
A.OrderID,
A.ProductID,
A.UnitPrice,
A.Quantity
FROM [Order Details] AS A
ORDER BY A.OrderID, A.ProductID
;


For a custom function, I'd use Static variables, along the lines of this
air code:

Public Function FIDAssign(Group As Variant) As Variant
Static SeqNum As Long
Static LastGroup As Variant

'Null group resets counter and returns Null
If IsNull(Group) Then
SeqNum = 0
LastGroup = Null
FIDAssign = Null
Exit Function
End If

'New group resets counter
If Group <> LastGroup Then
SeqNum = 0
LastGroup = Group
End If

SeqNum = SeqNum + 1
FIDAssign = SeqNum
End Function
 
Back
Top