How to Add Sequenced Number Based on Sorted Value in Query in Access

How to add sequenced number based on sorted value in query in Access

Assuming a table name of table1, The following should yield the desired result:

select a.att1, (select count(*) from table1 b where b.att1 <= a.att1) as att2
from table1 a;

For every record, the query calculates the number of records less than or equal to the current record, which is then output as the sort index.

Add sequence number to query results

In MS Access, you can get this information using a correlated subquery:

select incidentid_uk, personid_uk,
       (select count(*)
        from table as t2
        where t2.incidentid_uk = t.incidentid_uk and
              t2.personid_uk <= t.personid_uk
       ) as sequence
from table as t;

MS Access Database, Sequential Number in Select query

I would recommend do not use SQL to calculate such data. It's bad practice.
It would better to add a field into table and perform calculations before sql statement execution if needed.

Anyway can implement function in VBA to do this. Like:

Private m_counter as Integer

Function CounterInit(Byval counter as Integer)
   m_counter = counter
End Function

Function CounterGetNext(Byval incr as Integer)
   m_counter = m_counter + incr

   CounterGetNext = m_counter
End Function

And then use CounterGetNext() in SQL. But you still have to call CounterInit() before sql execution. Use RecordsetType = Snapshot for such query.

Microsoft Access - Create a numerical sequence based on field value changes?

Take a look here. I think the author has solved some very similar issues.

Microsoft Access query returning sequential numbers

You can use a simple Cartesian Query having a table with numbers from 0 to 9:

SELECT 
    t1.Number + 
    t2.Number * 10 + 
    t3.Number * 100 + 
    t4.Number * 1000 As Factor
FROM 
    tblNumber AS t1, 
    tblNumber AS t2,
    tblNumber AS t3, 
    tblNumber AS t4

or - if you only need small series - just a table with numbers from 0 to 99.

Then for your samples:

SELECT 
    10 + 0.1 * [Number] AS [Value]
FROM 
    tblNumber
WHERE 
    [Number] Between 1 And 5

and:

SELECT 
    [Number] * 10 AS [Value]
FROM 
    tblNumber
WHERE 
    [Number] Between 2 And 10
ORDER BY 
    [Number] * 10 Desc

and:

SELECT 
    DateAdd("h", [Number], #2015-04-10 12:00 PM#) AS [Date]
FROM 
    tblNumber
WHERE 
    [Number] Between 0 And 6

Addendum

A number series can also be built without a dedicated number table by using a system table as source:

SELECT DISTINCT 
    [Tens]+[Ones] AS Factor, 
    10*Abs([Deca].[id] Mod 10) AS Tens, 
    Abs([Uno].[id] Mod 10) AS Ones
FROM 
    msysobjects AS Uno, 
    msysobjects AS Deca;

Inserting a rank in sequential order for grouped records in a table

I found it! No need to keep looking. The following loop routine is exactly what i needed to resolve this problem. Applying this to my 'generic/theory' example above, you can see how the 'sequence' field above is reflected by the field 'ID_Event' in the below routine. The loop checks the prior record to and if it is has the same date ('Effective Date') it adds 1 to the field 'ID_Event'

Private Sub Comando197_Click()

Dim sql As String
Dim varEffective_Date As Date
Dim NumberOfTimes
Dim varID_Event As Integer

sql = "select ID_Event,ID_ValueAddWaste,Effective_Date,ValueAddType,ValueAddType02 from tblPerformanceTrackingMaster03 where tblPerformanceTrackingMaster03.[ID_Event]=0 order by ID"

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

rs.Open sql, CurrentProject.Connection, adOpenDynamic, adLockPessimistic

Dim counter As Integer
counter = 1

Do While Not rs.EOF

If counter > 1 Then

    If varEffective_Date = rs!Effective_date Then
    rs!ID_Event = varID_Event + 1

    End If
End If

varEffective_Date = rs!Effective_date
varID_Event = rs!ID_Event

rs.MoveNext
counter = counter + 1

Loop
rs.Close
Set rs = Nothing

End Sub

SQL Query in MSAccess to Rank a Value Column with Letters based on it's Sort Order

A very general solution for a very general question:

If you have well-defined ordering (you order by a column that doesn't have duplicates) and grouping, you can use a subquery to achieve this:

It would look like this:

SELECT 
    (
        SELECT COUNT(*)
        From MyTable s
        WHERE 
            s.GroupingColumn1 = m.GroupingColumn1
            AND s.GroupingColumnN = m.GroupingColumnN
            AND s.SortingColumn1 <= m.SortingColumn1
    )
FROM MyTable m
GROUP BY GroupingColumn1, GroupingColumnN
ORDER BY SortingColumnN

That gets you the position of the items within the groups.

You can easily convert this to capital letters using a little knowledge of the ASCII table (A = position 65, capitals are all sequential, so by incrementing the position by 64 and looking up the ASCII character for the position, you'll get A for 1, B for 2, etc)

Chr(MyPosition + 64)

Of course, if the table is stored in a backend that supports window functions, this can be done more clearly, concisely, and faster. Unfortunately, Access does not support them.

Ordering should be implemented using > and <, which makes the statement fairly long for multiple ordering conditions:

SELECT M.[FILENAME], M.[ZONE],M.[VALUECOL],

CHR(64 +  (
        SELECT COUNT(*) 
        FROM tblTest AS S
        WHERE 
            (S.[FILENAME] = M.[FILENAME])
            AND (
                           (s.VALUECOL > m.VALUECOL)
                          OR (
                                    (s.VALUECOL = m.VALUECOL) AND (s.ZONE <= m.ZONE)
                                )
                       )
            ) ) AS LETTER
FROM tblTest AS M
GROUP BY M.[FILENAME], M.[ZONE], M.[VALUECOL]
ORDER BY M.[FILENAME] ASC, M.[VALUECOL] DESC,M.[ZONE] ASC

---

Related Topics