Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Sep 2001
    Location
    SC, USA
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Number query results by group (2000)

    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...???

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Number query results by group (2000)

    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?

  3. #3
    Star Lounger
    Join Date
    Sep 2001
    Location
    SC, USA
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Number query results by group (2000)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Number query results by group (2000)

    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:
    <UL><LI>SourceName is the name of a query. It must be sorted by the group field you want to use for counting.
    <LI>KeyName is the name of a field that uniquely identifies records (an AutoNumber field for instance).
    <LI>KeyValue is the value of this field in the record whose number you want to determine.
    <LI>GroupValue is the name of a field that uniquely identifies the groups.
    <LI>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.
    Attached Files Attached Files

  5. #5
    Star Lounger
    Join Date
    Sep 2001
    Location
    SC, USA
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Number query results by group (2000)

    Thanks Hans, you've really made my day!

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Number query results by group (2000)

    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.

  7. #7
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Number query results by group (2000)

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

  8. #8
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Number query results by group (2000/97)

    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! <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>
    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
    Attached Files Attached Files

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Number query results by group (2000/97)

    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.

  10. #10
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Number query results by group (2000/97)

    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 :-).)

  11. #11
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Number query results by group (2000/97)

    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 <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23> you might tweak another second or two from this result, but that's it, I guess <img src=/S/hushmouth.gif border=0 alt=hushmouth width=16 height=16>.

    My conclusion: the function's time loss seems inevitable when so many records are involved, unless a bright mind <img src=/S/clever.gif border=0 alt=clever width=15 height=15> could avoid loading the entire recordset or provide an entirely different approach <img src=/S/salute.gif border=0 alt=salute width=15 height=20>.

    Greetings,
    Hasse

    (1) provided by Excel's helpful CTRL+D <img src=/S/artist.gif border=0 alt=artist width=34 height=29> let-me-fill-down-zillions-of-lines-with-nonsense-feature

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •