Number query results by group (2000)

Topic

New Reply

I know you can number the results of a query (1,2,3,4…..), but is it possible to begin the count again within the query, based on a value change? Example: Pet_Names_Qry: Dogs: 1. Rex, 2. Rover, 3. Ruff. Cats: 1. Tabby, 2. Snow Bell, etc…???

Reply | Quote

Replies

It can probably be done in a query, but it’s rather artificial. It is very easy in a report: add a grouping level for species (or whatever the field you want to group on is named). Put a text box in the detail section with Control Source =1, and set its Running Sum property (in the Data tab) to Over Groups. Can you live with that?

Reply | Quote

I really want to do this in a query if possible. I’ll restate this again: Can you number records in a query by a group? Example:
______Black: 1. Fred
___________2. Mary
______White: 1. Joe
___________2. Sue
___________3. Bob
___Hispanic: 1. Sarah
___________2. Ralf

Reply | Quote

OK, here you go, but what I have come up with is s l o o o w w w.

You will need a unique identifier for the groups and a unique identifier for the records. These are used in the function below; it is an extended version of an example from Microsoft.

Function GetLineNumber(SourceName As String, KeyName As String, _
KeyValue, GroupName As String, GroupValue) As Long

Dim rst As DAO.Recordset
Dim lngCount As Long
Dim strWhere As String

On Error GoTo Err_GetLineNumber

Set rst = CurrentDb.OpenRecordset(SourceName)
strWhere = BuildCriteria(GroupName, rst.Fields(GroupName).Type, GroupValue)
rst.Close

Set rst = CurrentDb.OpenRecordset(“SELECT * FROM ” & SourceName & ” WHERE ” & strWhere)

‘ Find the current record.
rst.FindFirst BuildCriteria(KeyName, rst.Fields(KeyName).Type, KeyValue)

‘ Loop backward, counting the lines.
Do Until rst.BOF
lngCount = lngCount + 1
rst.MovePrevious
Loop

Bye_GetLineNumber:
‘ Return the result.
GetLineNumber = lngCount
rst.Close
Set rst = Nothing
Exit Function

Err_GetLineNumber:
lngCount = 0
Resume Bye_GetLineNumber
End Function

This function takes 5 arguments:

• SourceName is the name of a query. It must be sorted by the group field you want to use for counting.
• KeyName is the name of a field that uniquely identifies records (an AutoNumber field for instance).
• KeyValue is the value of this field in the record whose number you want to determine.
• GroupValue is the name of a field that uniquely identifies the groups.
• GroupValue is the value of this field in the record whose number you want to determine.
  [/list]The function uses DAO, so you must set a reference to the Microsoft DAO 3.x Object Library in the Visual Basic Editor (Tools | References…)

  You must first create a query that sort the records by group. Then create a new query based on the first one; add a calculated field with the above function. For example:

  N:GetLineNumber(“qryProducts”,”ProductID”,[ProductID],”CategoryID”,[CategoryID])

  to calculate a line number in a query based on qryProducts; ProductID is the record identifier and CategoryID is the group identifier.

  I have attached a zipped Access 97 database that demonstrates this using the Categories and Products tables from the Northwind sample database.

Reply | Quote

Thanks Hans, you’ve really made my day!

Reply | Quote

Another way to do this is to populate a temporary table initially with the data required in the order required, then update the table with the number (done in VBA). Then just build a query based upon the temporary table.

Reply | Quote

Thanks for this usefull thread!
Hans, in case you have some idea’s for speeding up the process but no time, let it know! Maybe I (or others) can give it a try…

Reply | Quote

Would you mind to have a look at the attachment? I might have found a faster solution .
However, testing the function in Hans’ previous solution generates an error!
Abbreviation used: P(seudo)A(uto)N(umber)

There are two functions: one which ‘autonumbers’ all through the recordset and one that restarts when a new group begins (listed below).

Further limitations:
– use only in calculated fields in queries with a unique key (ID)
– no renumbering when user adds custom sort order using the ‘A-Z’ & ‘Z-A’-buttons in datasheet view! Function uses actual SQL

Further to do:
– error handling
– use one PAN(…) function with optional ‘grouping’ argument (requires some rewriting I guess)
– check out: behaviour when user enters new records in query & application in forms,…
– large recordsets: use ‘findfirst’ to go to first record of group X (faster then looping all through)

Public Function PAN_Group(QueryName As String, KeyName As String, KeyValue As Variant, GroupField As String) As Long

Dim rst As Recordset ‘DAO.Recordset
Dim lngI As Long
Dim varCurGroup

‘Exit Function ‘Programming help: when error occurs, it repeats itself for each record. So: with first occurence choose ‘debug’, remove the “‘” at the beginning of this line and save before stopping execution (otherwise, an error will pop up for each record!)

‘Initialize
Set rst = CurrentDb.OpenRecordset(QueryName, dbOpenDynaset)
rst.MoveLast
rst.MoveFirst
lngI = 1
PAN_Group = 0
varCurGroup = rst(GroupField)

‘Loop through query
Do While Not rst.EOF
‘Start renumbering when new group starts (i.e. when the ‘group’ field changes to a new value)
If Not rst(GroupField) = varCurGroup Then
lngI = 1
varCurGroup = rst(GroupField)
End If
‘If current record is the one with the required ID value, then pseudo-autonumber (& exit)
If rst(KeyName) = KeyValue Then
PAN_Group = lngI
Exit Function
End If
rst.MoveNext
lngI = lngI + 1
Loop

End Function

Reply | Quote

I had no problem appying your functions in my example. On large recordsets, your method is slightly faster, but both are excruciatingly slow. On a test with a table with a million records, it took 2 minutes and 13 seconds before I regained control with my method, and 2 minutes and 8 seconds with your method.

Reply | Quote

Argl… are you serious, a million records? I thought Access was just a horse to pull max. some tens of thousands!
Still you’re absolutely right: two minutes is no fun. Have you already tried to use ‘findfirst’ to jump to the first appearance of the group value in the recordset (and start counting there) instead of looping the whole set through one by one before you get there? (Just if you are interested… otherwise I’ll try it out myself – I’ll just have to be creative in order to arrange a million records :-).)

Reply | Quote

Argl… are you serious, a million records? I thought Access was just a horse to pull max. some tens of thousands!
Still you’re absolutely right: two minutes is no fun. Have you already tried to use ‘findfirst’ to jump to the first appearance of the group value in the recordset (and start counting there) instead of looping the whole set through one by one before you get there? (Just if you are interested… otherwise I’ll try it out myself – I’ll just have to be creative in order to arrange a million records :-).)

Reply | Quote

Hans, (and all those others which might be interested)

FYI: apparently (but reasonably too, probably) most time is just spent by loading the recordset. When I ran a 350.000 records query (1) using the function truncated after the ‘set rst = …’ line (so: doing no more than assigning the recordset to a variable), it still needed more than 10″ per individual function call (coming close to your previous measurement).
With a dbOpenForwardOnly recordset type you might tweak another second or two from this result, but that’s it, I guess .

My conclusion: the function’s time loss seems inevitable when so many records are involved, unless a bright mind could avoid loading the entire recordset or provide an entirely different approach .

Greetings,
Hasse

(1) provided by Excel’s helpful CTRL+D let-me-fill-down-zillions-of-lines-with-nonsense-feature

Reply | Quote

Hans, (and all those others which might be interested)

FYI: apparently (but reasonably too, probably) most time is just spent by loading the recordset. When I ran a 350.000 records query (1) using the function truncated after the ‘set rst = …’ line (so: doing no more than assigning the recordset to a variable), it still needed more than 10″ per individual function call (coming close to your previous measurement).
With a dbOpenForwardOnly recordset type you might tweak another second or two from this result, but that’s it, I guess .

My conclusion: the function’s time loss seems inevitable when so many records are involved, unless a bright mind could avoid loading the entire recordset or provide an entirely different approach .

Greetings,
Hasse

(1) provided by Excel’s helpful CTRL+D let-me-fill-down-zillions-of-lines-with-nonsense-feature

Reply | Quote

I had no problem appying your functions in my example. On large recordsets, your method is slightly faster, but both are excruciatingly slow. On a test with a table with a million records, it took 2 minutes and 13 seconds before I regained control with my method, and 2 minutes and 8 seconds with your method.

Reply | Quote

Would you mind to have a look at the attachment? I might have found a faster solution .
However, testing the function in Hans’ previous solution generates an error!
Abbreviation used: P(seudo)A(uto)N(umber)

There are two functions: one which ‘autonumbers’ all through the recordset and one that restarts when a new group begins (listed below).

Further limitations:
– use only in calculated fields in queries with a unique key (ID)
– no renumbering when user adds custom sort order using the ‘A-Z’ & ‘Z-A’-buttons in datasheet view! Function uses actual SQL

Further to do:
– error handling
– use one PAN(…) function with optional ‘grouping’ argument (requires some rewriting I guess)
– check out: behaviour when user enters new records in query & application in forms,…
– large recordsets: use ‘findfirst’ to go to first record of group X (faster then looping all through)

Public Function PAN_Group(QueryName As String, KeyName As String, KeyValue As Variant, GroupField As String) As Long

Dim rst As Recordset ‘DAO.Recordset
Dim lngI As Long
Dim varCurGroup

‘Exit Function ‘Programming help: when error occurs, it repeats itself for each record. So: with first occurence choose ‘debug’, remove the “‘” at the beginning of this line and save before stopping execution (otherwise, an error will pop up for each record!)

‘Initialize
Set rst = CurrentDb.OpenRecordset(QueryName, dbOpenDynaset)
rst.MoveLast
rst.MoveFirst
lngI = 1
PAN_Group = 0
varCurGroup = rst(GroupField)

‘Loop through query
Do While Not rst.EOF
‘Start renumbering when new group starts (i.e. when the ‘group’ field changes to a new value)
If Not rst(GroupField) = varCurGroup Then
lngI = 1
varCurGroup = rst(GroupField)
End If
‘If current record is the one with the required ID value, then pseudo-autonumber (& exit)
If rst(KeyName) = KeyValue Then
PAN_Group = lngI
Exit Function
End If
rst.MoveNext
lngI = lngI + 1
Loop

End Function

Reply | Quote

Thanks for this usefull thread!
Hans, in case you have some idea’s for speeding up the process but no time, let it know! Maybe I (or others) can give it a try…

Reply | Quote