[AccessD] Need Row Count in SQL for each Row

[Jim Dettman]

Brad,

 SELECT ROW_NUMBER() OVER(ORDER BY <field> ASC) as RowNum, <field>, <another
field> FROM <some table> WHERE....

  You'll have to run that as a pass through query though...JET/ACE doesn't
support ROW_NUMBER.

 If you can't do that, then your stuck with a VBA call.   Ordinarily you'd
use a sub-select, but you need a column to sequence things.

Jim.   

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Brad Marks
Sent: Thursday, April 06, 2017 11:48 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Need Row Count in SQL for each Row

All,

In the 1970s, I worked with some mainframe database software from Cincom
called "Total" which was later repackaged as "Supra".  Total and Supra were
"network" databases which had a file type called "Variable" which stored
records on "chains".  One of the downfalls of these databases was that the
physical position of a record on a chain had meaning.  Often reading down a
chain of records would indicate the chronological order of the records.

After leaving the world of Total and Supra, I thought that I would never
again work with files where the physical position of the record held
meaning.

I now face a similar challenge.  I am trying to read data from a file where
the order of the records has meaning.  Unfortunately there is no field in
the records that can be used to retain the order.

I have a simple SQL statement that pulls the data from this file.  I would
like to add a column in the SQL to keep track of the order of the records
for subsequent processing.  I know that I can do this via a function in
Access.

Here is the catch.  I have been instructed to not use any "special" Access
features in the SQL that are not available in "vanilla" SQL, because there
is concern about being too locked into Access in case the shop moves to
another platform.

Is there a way to add a column in an SQL statement to keep track of "record
count" via vanilla SQL?

I have done some digging and have not found anything yet.

Thanks,
Brad

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com