[AccessD] Query - Add Recordcount Field

Mitsules, Mark Mark.Mitsules at ngc.com
Thu Nov 11 13:26:54 CST 2004


Jim,

Would you be willing to look at the SQL off-list?

Mark



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


Mark,

  Still should have numbered each row in the final output. I just tried it
here and it works fine.

SELECT tblLookupControl.FormName, GetLineCounter([FormName]) AS LineNumber
FROM tblLookupControl
GROUP BY tblLookupControl.FormName;

  I'm not clear on how you ended up with the same result with the two
methods either.  If I do:

SELECT tblLookupControl.FormName, Count([FormName]) AS LineNumber
FROM tblLookupControl
GROUP BY tblLookupControl.FormName;

  I get the number of occurrences within each group.

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