[AccessD] Query - Add Recordcount Field

Mark,

    Following alternatives could be considered, for getting running count in a query field -

    (a) Sub-query.
    (b) Domain function i.e. DCount().
    (c) User defined function based upon recordset.
    (d) User defined function based upon sequentially incrementing global variable.

    Use of sub-query for running count renders the output non-updateable. If this is considered a constraint, options (b) or (c) could be tried. In all cases, it is necessary that the fields upon which running count is based, are sorted in proper sequence. The last field amongst the group of sorted fields should be the one holding unique values (so as to prevent duplicate values in running count).

    Option (d) needs special care in view of volatile nature of output. In a select query, it results in inconsistent display, as the calculated result keeps on changing if the user navigates over it repeatedly from. As such, its use is preferably restricted only to single pass action queries (e.g. Append or update).

    Two sample SQL's using sub-query approach are given at (A) and (B) below. (A) represents the simplest case where the running count is based upon only one field (named ID) having unique values, while (B) demonstrates a combination of date field (TDate) and the unique value field (ID).

    The samples are based upon table T_Data as the source. When running count is needed on relatively complex query (specially if it is aggregate type involving group by clause), the final query should be used as source (in place of T_Data).

Regards,
A.D.Tejpal
--------------

(A)
=================================
SELECT (Select Count(*) From T_Data As T1 Where T1.ID <= T_Data.ID) AS RunningCount
FROM T_Data
ORDER BY T_Data.ID;
=================================

(B)
=================================
SELECT (Select Count(*) From T_Data As T1 Where T1.TDate < T_Data.TDate Or (T1.TDate = T_Data.TDate And T1.ID <= T_Data.ID)) AS RunningCount
FROM T_Data
ORDER BY T_Data.TDate, T_Data.ID;
=================================

  ----- Original Message ----- 
  From: Mitsules, Mark 
  To: 'Access Developers discussion and problem solving' 
  Sent: Friday, November 12, 2004 01:59
  Subject: RE: [AccessD] Query - Add Recordcount Field


  Yes, for each record returned by the query.
  In the example below, it would be fldItem.

  A simple example:
  fldItem  fldName  fldScore
  1        Tom      90
  2        Dick     95
  3        Harry    93

  Mark

  -----Original Message-----
  From: John W. Colby [mailto:jwcolby at colbyconsulting.com] 
  Sent: Thursday, November 11, 2004 1:42 PM
  To: 'Access Developers discussion and problem solving'
  Subject: RE: [AccessD] Query - Add Recordcount Field

  You want just an incrementing number?

  John W. Colby
  www.ColbyConsulting.com 

  Contribute your unused CPU cycles to a good cause:
  http://folding.stanford.edu/

  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
  [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mitsules, Mark
  Sent: Thursday, November 11, 2004 1:08 PM
  To: 'Access Developers discussion and problem solving'
  Subject: RE: [AccessD] Query - Add Recordcount Field

  John, Jim,

  Gentlemen...we have a problem:)

  Both of these approaches produce the same results (probably a good thing), however the results are not what I expected.  My uneducated guess is that I should have mentioned that this is for an aggregate query.  So what these methods end up producing is a count of each GroupBy.  Any suggestions?

  Mark


  -----Original Message-----
  From: Jim Dettman [mailto:jimdettman at earthlink.net] 
  Sent: Thursday, November 11, 2004 11:35 AM
  To: Access Developers discussion and problem solving
  Subject: RE: [AccessD] Query - Add Recordcount Field


  Mark,

    I think your saying you want the records numbered?

    I use a custom function:

  Global glngGetLineCounter As Long

  Function GetLineCounter(varName As Variant) As Long

    glngGetLineCounter = glngGetLineCounter + 1
    GetLineCounter = glngGetLineCounter

  End Function

    Which I call from the query

  LineNo:GetLineCounter(<somefieldname>)

    If you need this in a form or report though, add a control with a running sum defaulted to 1.

  Jim Dettman

  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
  [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Mitsules, Mark
  Sent: Thursday, November 11, 2004 10:40 AM
  To: [AccessD]
  Subject: [AccessD] Query - Add Recordcount Field

  Group,

  Someone has this at the top of their head or near their
  fingertips...hopefully.  In a query, what is the syntax to add a running recordcount column?  Thanks.

  Mark