CODEWITHSUNDEEP
ms-access
user2821450
user2821450

Reputation: 11

Return a incremental group number per group in MS Access

I need help to create a query in Access to incrementally number groups of rows, grouped on a common date and keep the "group numbers" increment on the next date like showing in the Result column.

Date |ID |Result

2017/01/09 |809 | 1

2017/01/09 |810 | 1

2017/01/09 |826 | 1

2017/01/10 |826 | 2

2017/01/11 |809 | 3

2017/01/11 |810 | 3

2013/01/11 |826 | 3

2017/01/12 |809 | 4

2017/01/12 |810 | 4

2017/01/12 |826 | 4

Thank you

Upvotes: 1

Views: 3016

Answers (2)

Gustav
Gustav

Reputation: 55816

You can use this function which is for exactly the purpose. See in-line comments for typical usage:

Public Function RowCounter( _
  ByVal strKey As String, _
  ByVal booReset As Boolean, _
  Optional ByVal strGroupKey As String) _
  As Long

' Builds consecutive RowIDs in select, append or create query
' with the possibility of automatic reset.
' Optionally a grouping key can be passed to reset the row count
' for every group key.
'
' Usage (typical select query):
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));
'
' Usage (with group key):
'   SELECT RowCounter(CStr([ID]),False,CStr[GroupID])) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));
'
' The Where statement resets the counter when the query is run
' and is needed for browsing a select query.
'
' Usage (typical append query, manual reset):
' 1. Reset counter manually:
'   Call RowCounter(vbNullString, False)
' 2. Run query:
'   INSERT INTO tblTemp ( RowID )
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable;
'
' Usage (typical append query, automatic reset):
'   INSERT INTO tblTemp ( RowID )
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter("",True)=0);
'
' 2002-04-13. Cactus Data ApS. CPH
' 2002-09-09. Str() sometimes fails. Replaced with CStr().
' 2005-10-21. Str(col.Count + 1) reduced to col.Count + 1.
' 2008-02-27. Optional group parameter added.
' 2010-08-04. Corrected that group key missed first row in group.

  Static col      As New Collection
  Static strGroup As String

  On Error GoTo Err_RowCounter

  If booReset = True Then
    Set col = Nothing
  ElseIf strGroup <> strGroupKey Then
    Set col = Nothing
    strGroup = strGroupKey
    col.Add 1, strKey
  Else
    col.Add col.Count + 1, strKey
  End If

  RowCounter = col(strKey)

Exit_RowCounter:
  Exit Function

Err_RowCounter:
  Select Case Err
    Case 457
      ' Key is present.
      Resume Next
    Case Else
      ' Some other error.
      Resume Exit_RowCounter
  End Select

End Function

For your case it could be:

SELECT RowCounter(CStr([ID]), False, Format([Date], "yyyymmdd") AS RowID, *
FROM YourTable
WHERE (RowCounter(CStr([ID]), False) <> RowCounter("", True));

Upvotes: 0

June7
June7

Reputation: 21370

Here is one approach:
SELECT A.*, (SELECT Count(*) FROM (SELECT DISTINCT [Date] FROM Table1) AS Q1 WHERE A.Date>=[Date]) AS GrpSeq FROM Table1 AS A;

Another is with properties of textbox in Group Header on report:
RunningSum: OverGroup
ControlSource: =1

Upvotes: 2

Related Questions