Access 2013 Row Count equivalent?

Question

Hello all, 

For the life of me I can't seem to figure out how to do something I would assume to be simple in Access. 

I want to be able to add a row number for ordering in a query. I've found an example that just copies my id column and my current solution does not really work. 

the example found:

DCount(``"[ID]"``,``"[tblNames]"``,``"[ID]<=" & [ID])

My Current solution is to just create a table on the front end that I delete and reset the counter on an autonumber field. but if for any reason that table is open when I want to reset it, the table fails to update the counter.

Is there any solution to add a simple row 1 = 1; row 2 = 2; etc. to a query? I have the table working the way I want, but when selecting, Access adds undesired sorting on the indexed columns.... even with an explicit ORDER BY statement...

Answer 1

...........do I need something special to download off onedrive?

No, it's a straightforward download.  If you are trying to download it at work, however, your system might not allow downloads from OneDrive for fear of introducing malicious content.  What people generally do in that situation is download the file at home and email it to themself at work, or transport it via a portable medium.

The following are three queries from the demo which order rows sequentially by transaction date, using the primary key TransactionID as the tie-breaker where two or more transactions take place on the same day.  The first two are non updatable, whereas the last is updatable, do can be used as a form's RecordSource for instance, where data is to be inserted or edited in the form.  This is far less efficient than the first two, however, due to the DCount function calls.  Of the first two, that using a JOIN is more efficienmt than that using a subquery.

SELECT COUNT(*) AS RowNumber, T1.TransactionDate, T1.CustomerID, T1.TransactionAmount

FROM Transactions AS T1 INNER JOIN Transactions AS T2

ON (T2.TransactionID<=T1.TransactionID OR T2.TransactionDate<>T1.TransactionDate)

 AND (T2.TransactionDate<=T1.TransactionDate)

GROUP BY T1.TransactionDate, T1.TransactionID, T1.TransactionAmount, T1.CustomerID;

SELECT

  (SELECT COUNT(*)

    FROM Transactions AS T2

    WHERE T2.TransactionDate  <=  T1.TransactionDate

    AND ( T2.TransactionID <= T1.TransactionID

    OR T2.TransactionDate <> T1.TransactionDate)) AS RowNumber,

T1.CustomerID, T1.TransactionDate, T1.TransactionAmount

FROM Transactions AS T1

ORDER BY T1.TransactionDate, T1.TransactionID;

SELECT DCOUNT("*","Transactions","TransactionDate  <=  #"

& Format(TransactionDate,"yyyy-mm-dd") & "# 

AND (TransactionID <= " & TransactionID & "  OR TransactionDate <> #"

& Format(TransactionDate,"yyyy-mm-dd") & "#)") AS RowNumber,

CustomerID, TransactionDate, TransactionAmount

FROM Transactions

ORDER BY TransactionDate, TransactionID;

Fundamental to ordering rows dynamically in a query's result table is that the result table must be ordered on the basis of values in one or more columns.  It is the order which determines the numbers.  If you wanted to number the result table of a query which is not specifically ordered, then you would have to create a base table, much as you are doing, but without relying on an autonumber to assign the 'row numbers' or deleting and recreating the table each time; you'd just empty and refill it.  This would be done by including a column of straightforward integer number data type in the temporary table, and then executing some VBA code which establishes and steps through a recordset of the table's rows, and assigns an incrementing integer number to each row.  I do something similar, for instance, in a form based on a table of regular payments from an account, which are made in a specific order determined by a PaymentIndex column:

Private Sub cmdReindex_Click()

    Dim rst As DAO.Recordset

    Dim strSQL As String

    Dim strRS As String

    Dim n As Integer

    Me.Dirty = False

    strSQL = "SELECT PaymentIndex FROM RegularPayments ORDER BY PaymentIndex"

    Set rst = CurrentDb.OpenRecordset(strSQL)

    With rst

        .MoveLast

        n = 1

        .MoveFirst

        Do While Not .EOF

            .Edit

            .Fields("PaymentIndex") = n

            .Update

            n = n + 1

            .MoveNext

        Loop

    End With

    Set rst = Nothing

    Me.Requery

End Sub

Answer 2

Forgive me if I'm missing something obvious... but I cannot seem to download it... do I need something special to download off onedrive?

Answer 3

See RowNumbering.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169