[AccessD] Need Row Count in SQL for each Row

Darryl Collins darryl at whittleconsulting.com.au
Thu Apr 6 16:35:26 CDT 2017


Yes, I would have thought this the obvious and cheapest solution, but maybe there is a reason you cannot just sequentially key it up.

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Friday, 7 April 2017 7:19 AM
To: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Subject: Re: [AccessD] Need Row Count in SQL for each Row

An autonumber primary key aka Identity column will do this. (And you should  already have one in the table <g> )

https://docs.microsoft.com/en-us/sql/relational-databases/tables/create-primary-keys
and


So will a Timestamp if you want to track "last edit" rather than "create" sequence.

https://msdn.microsoft.com/en-us/library/ms182776(v=sql.90).aspx


On 6 Apr 2017 at 15:47, Brad Marks wrote:

> 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
> 



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



More information about the AccessD mailing list