Adding a Sequence Number to a table

[scottpaul4000]

I am trying to update a table by adding a sequence number that
increments by 1 but resets upon a change in Vendor Number. Here some
data:

VendorNumber Vendor Name Division
0001 Land Dev 81
0002 Martin's Systems 11
0002 Martin's Systems 30
003L Home Builders 05
003L Home Builders FA
003L Home Builders 12

Here's what I'm shooting for:

VendorNumber Vendor Name Division Seq
0001 Land Dev 81 1
0002 Martin's Systems 11 1
0002 Martin's Systems 30 2
003L Home Builders 05 1
003L Home Builders FA 2
003L Home Builders 12 3

So far I've tried and adapted various Count and DCount statements I've
found in this forum but the best I can get is a Sequence number that
continully grows or doesn't reset properly on a Vendor Number change.

Any help would be greatly appreciated.

Scott

 

[John Spencer]

Perhaps you need a calculated field that looks something like the following.

1 + DCount("VendorNumber","YourTableName","VendorNumber=""" & [Your
Table].[VendorNumber] & """ AND Division < """ & [Your Table].[Division] &
"""")


I'm assuming that you are attempting to do this in an update query.

UPDATE [Your Table]
SET [Your Table].[SEQ] = 1 +
DCount("VendorNumber","YourTableName","VendorNumber=""" & [Your
Table].[VendorNumber] & """ AND Division < """ & [Your Table].[Division] &
"""")

 

[scottpaul4000]

John,

The query seems to hang. Here is the actual query edited for my table
and fields:

UPDATE Temp SET Temp.SEQ =
1+DCount("Vendor_Number","Temp","Vendor_Number=""" & Temp.Vendor_Number
& """ AND Division < """ & Temp.Division & """");

The temp table has a large number of records, almost 200k, can you see
any reason for it to do so?

Scott

 

[John Spencer]

With that many records this will be S L O W.

So test it by limiting it to see if it does work at all.

UPDATE Temp
SET Temp.SEQ = 1+DCount("Vendor_Number","Temp","Vendor_Number=""" &
Temp.Vendor_Number
& """ AND Division < """ & Temp.Division & """"
WHERE Temp.Vendor_Number = "003L"

If that works, then you could try doing the update in ranges. That is

WHERE Temp.Vendor_Number between "0001" and "0500" and Temp.Seq is Null.

The real question here is why are you doing this? What benefit do you gain
in your database by assigning a sequence number to the records.

You can do ranking in query on a limited set of records.

You can use a control in a report that will assign a sequence number within
a group and reset for the next group.

 

[scottpaul4000]

Beautiful! That works. I'll run the rest of the query in chunks. Thanks
for your help.

Scott