[AccessD] ms-access variant of @@rowcount or rowcount

Here's an example we have used in the past. It may be useful.

Note : The REQUIREMENT that est_seq in example must have unique values

Query: MyRowNum

SELECT CCL_EST_NM.est_no, CCL_EST_NM.est_seq, CCL_EST_NM.est_nm,
 CCL_EST_NM.est_typ, CCL_EST_NM.est_lang, 
(Select Count (*) FROM [CCL_EST_NM] as Temp 
WHERE [Temp].[est_seq] < [CCL_EST_NM].[est_seq])+ 1 AS RowNum
FROM CCL_EST_NM;

REQUIREMENT:::: est_seq is a field within the Table that has unique
values in ascending order. The numbers do not have to be contiguous. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart
McLachlan
Sent: Monday, December 4, 2006 9:44 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] ms-access variant of @@rowcount or rowcount

On 4 Dec 2006 at 6:30, Sad Der wrote:

> Hi,
> 
> in my query i need a field with the rowcount.
> 
> Let's say i've got a table (t1) with 1 field (Name).
> This table has 2 values (a & b).
> 
> I want a query that returns the field and a rowcount e.g.:
> select name, row_count
> from t1
> 
> Problem: what is the access variant for @@ROWCOUNT??

Count(*) ?-- 
Stuart


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