Write back sort order to a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Write back sort order to a table

Author

Topic

benking9987
Posting Yak Master

124 Posts

Posted - 2010-04-26 : 16:48:08

I have a query that sorts based on criteria from an ORDERS_Header Table and an ORDERS_Lines table. I want to pull a report that I'm formatting for a packing slip on these orders, but because I am using MS Access as my front end I have to choose a proper "Group" field. The ORDERS_Header table contains all the header information for the orders like bill to name, ship to name, etc. The ORDERS_Lines table contains the item information since there can be one-or-many Lines for any one order.

With that said, the query pulls together all orders with lines in preparation for the report, But in order for each line to be printed on a page with the appropriate order header, I need a "Group" field. I am calling that field Print ID. It is CRITICAL to print the packing slips in the correct order, which is why this whole problem comes up. I want my "Group" field to be my Print ID, which is essentially the order of a certain query. So, if my query sends out the following records in this order:

ABC (1)
DEF (2)
GHI (3)

Then I want to be able to write back the (1), (2), (3) to the original ORDERS_Header table.

I gave a lot of background, but essentially the question is simple. I need to generate numeric sequence from a query and then write that sequence back to the corresponding records on the Orders_header table.

A further caveat, I need the order to be 3 or 4 digits long (ie 0001, 0002, 0003, 0004, 0005, 0006, etc). Otherwise the grouping doesn't occur properly on the MS Access report.

Any ideas on how to proceed?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-26 : 16:57:54

If you are using SQL Server 2005/2008, then you can use ROW_NUMBER() function. It would be a numeric field though, but you can add in your leading zeros at presentation time.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

benking9987
Posting Yak Master

124 Posts

Posted - 2010-04-26 : 17:15:00

I am using 2005. I'll check into the syntax of the function you mentioned and see if it works out. Didn't even know this was a possibility.

Thank you. I'll let you know how it goes.

benking9987
Posting Yak Master

124 Posts

Posted - 2010-04-26 : 17:40:26

I think this is going to be what I need. Thanks for your quick help.

Can you tell me how I pad the numbers? Here is what I have:

CAST (ROW_NUMBER() OVER (ORDER BY Reference_3) AS varchar(20)) as PrintID

Its giving me 1, 2, 3, 4, 5, etc.

I want 0001, 0002, 0003, 0004, 0005, etc.

benking9987
Posting Yak Master

124 Posts

Posted - 2010-04-26 : 17:43:02

Got. Sorry, should have looked around a little:

RIGHT (1000 + CAST (ROW_NUMBER() OVER (ORDER BY Reference_3) AS varchar(20)), 3) as PrintID

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-26 : 18:01:58