Assign Sequential Numbers within groups in a query

[casablues]

I am querying a large file of vendor records. For vendors with multiple records, I want to number them sequentially, starting over from 1 for each new vendor group:

IBM 1
IBM 2
IBM 3
NBC 1
NBC 2
CBS 1
ABC 1
FOX 1
FOX 2
FOX 3

There are no data fields within the record to indicate sequence. Thanks in advance for any assistance you can provide!

 

[Andrew Fergus]

Hi, I'm not sure if I understand the problem 100% and I'm curious as to what it is you are trying to achieve.

I can see that you want a unique identifier for each record, starting at 1 for each vendor. I'm not sure if you are trying to do this in a query (you mentioned "I'm querying a large file") or whether you want to store the unique number in a table.

I don't think you can do this (someone please correct me if I am wrong) in a query with any surety that you will get the same result each time (i.e. the first IBM record may be assigned the number 1 when you first run the query but then it might get another number when you run the query at a later time). I'm not sure how important the consistency of the numbering sequence is but if it isn't important then I question the value of using a unique number in this manner. What purpose will this unique number achieve?

If the number must be consistent (i.e. each record keeps getting assigned the same unique number) then I think this value should be stored in the table. Is that what you are looking for?

Can you provide some more information as to what it is you are trying to achieve, as well as the other fields in the table, and someone here should be able to help.

Andrew.

 

[casablues]

Andrew - thanks for the thoughtful questions you raised. This is a one time trasnsformation of vendor records for loading into a Peoplesoft database. I will be creating a sequential code for each vendor group record. Within each vendor group there is one main record and other subsidiary records.

I was able to export the table back to Excel and sequence them using an IF statement. It would be nice to know how to do this in Access when it is not practical to go back to Excel.

 

[Norie]

Vince

This code looks at a table called Vendors with two fields Vendor(Text) and VendorID(Number)

Sub CreateID()
Dim db As Database
Dim qdfGroup As QueryDef
Dim qdfUpdate As QueryDef
Dim rstGroup As Recordset
Dim rstUpdate As Recordset
Dim I As Integer

    Set db = CurrentDb

    Set qdfGroup = db.CreateQueryDef("GroupVendors", "SELECT Vendor FROM Vendors Group By Vendor")
    
    Set rstGroup = db.OpenRecordset("GroupVendors")
    
    rstGroup.MoveFirst
    
    While Not rstGroup.EOF
    
        Set qdfUpdate = db.CreateQueryDef("UpdateVendors", "SELECT VendorID  FROM Vendors WHERE Vendor = '" & rstGroup.Fields(0) & "'")
        
        Set rstUpdate = db.OpenRecordset("UpdateVendors")
    
        rstUpdate.MoveFirst
        
        I = 1
        While Not rstUpdate.EOF
            With rstUpdate
                .Edit
                .Fields(0) = I
                .Update
            End With
            
            I = I + 1
            
            rstUpdate.MoveNext
        Wend
        
        db.QueryDefs.Delete qdfUpdate.Name
        
        rstGroup.MoveNext
        
    Wend
    
    db.QueryDefs.Delete qdfGroup.Name
    Set db = Nothing
    Set rstGroup = Nothing
    Set rstUpdate = Nothing
End Sub

I tested it on your small sample and it seemed to do what you want.

 

[casablues]

Thanks for your reply, Norie. I have been out of town and not able to test your solution yet. But I appreciate the effort! I know I will run into this situation again in the near future.