Need Help on Sequential Numbering

[SeaninSeattle]

I need to have a script sequentially number an empty field whenever the same document number appears (I'm creating a shipping system). So if the same invoice number appears five times, the "packagenumber" field will be numbered 1,2,3,4,5 - etc.

I know how to do the unique id thing - but this seems easier - anyone know how to do it (books on line is of no help).

Thanks,
//sse Sean Engle
Admin/DirIS
seane@bswusa.com

 

[tlbroadbent]

Here is one possible way to do what you want. This method can be inefficient with large record sets so be cautious. Use a correlated sub-query to count the records. I assume you have a unique identifier or another column that can be used to order the result set.

SELECT
i.InvoiceNo, i.RecID, i.InvoiceQty,
(Select count(*) From InvoiceTbl
Where InvoiceNo=i.InvoiceNo
And RecID<=i.RecID) AS PackageNumber
FROM InvoiceTbl AS i
ORDER BY i.InvoiceNo, i.RecID;

Change the table and column names as needed. Let me know if you have questions. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[SeaninSeattle]

Excellent! Thanks so much! What would I do (what changes would I have to make) to make this an update statement, so that the field &quot;BoxNumber&quot; is actually updated with those box counts?

Thanks again -

Sean Sean Engle
Admin/DirIS
seane@bswusa.com

 

[tlbroadbent]

Could you post the table structure? That will make it easier to suggest a solution.

Thanks, Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[SeaninSeattle]

Actually, I just figured it out:

UPDATE SOPShipmentInfo
SET BoxNumber=(Select count(*) From SOPShipmentInfo Where DocumentNumber=i.DocumentNumber And ROWID<=i.ROWID)
FROM SOPShipmentInfo AS i


Thanks very much for your help!

//sse Sean Engle
Admin/DirIS
seane@bswusa.com