Jim Dettman
jimdettman at earthlink.net
Thu Nov 11 15:30:25 CST 2004
Mark, Sure. But on would be fine too; it is on topic. Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Mitsules, Mark Sent: Thursday, November 11, 2004 2:27 PM To: 'Access Developers discussion and problem solving' Subject: RE: [AccessD] Query - Add Recordcount Field 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 -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com