Advice on how to do this ?

[Olivia_Crazy_Horse]

I currently have a table in sql server with 600 records and growing. The table contains main columns ref_no,qty, etc. We plan to add another column for line items.So if ref_no 12345 currently is in the table five times with five different quantities, it would have line item numbers 1, 2, 3, 4, 5. If ref_no 99999 in in the table twice, then it would have line item numbers, 1,2. The boss wants me to manually add the line numbers for each ref_no. But this is rediculous, cumbersome and very tedious.

I know there is a better way to do this, just cant think of it. What is the most efficient way to add the line numbers for each ref_no ?

[Newsgroup_User]

i think moving quantities data to a separate table is a better approach...

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[Dan_Bracuk]

select sum(quantity)

[Guest]

First off, as Azadi said, it sounds like your data could be normalized into two or more table to deal with the data more efficiently.

To do what you describe you could try this:
Query your data.Make sure to get all records in the table and that the query is sorted on the ref_no column. This will ensure all the same ref_no are grouped together.

Use nested cfoutputs to step through your table, sequentially numbering the ref_nos that have the same value. (You don't actually have to have any code that displays anything but allows you to see what is happening). You can also do before and after queries and dump both to see what's happened with your data.