Product grouping on an itemorder table...

[mmorancbt]

I have a system tracking orders and their line items. My question is primarily about a sub-form of the items for an order, we will call it tblOrderItems

Each item has a unique key - identity integer field.

Each item can also be tied to a master item - accessories for a given item that is ordered. There is a field called parentitem_cd. This is set to 0 if the item is a master/top level item.

There are several joined and calculated fields to show on each line item on this form - which I have working.

What I do is I set the sub-form to Select string using data from the master form. I only reference unique key and the parent_cd in the select statement.

then, for each record, a set of calculated fields pulls the necessary data to display the full record.

How can I sort this to ensure that child items fall under their respective parent item. For instance:

OrderItem_uno | item          | parent_cd
13072           Pool            0  - this is a master item
13090           Skimmer       | 13072 - this is a child of Pool
13075           Phone         | 0 - master item
13080           Headset       | 13075

etc.

I hope this rambling question made sense. The most critical element is keeping child records sorted after their respective parent record.

Thanks.

Matthew Moran (career blog and podcast below)
Career Advice with Attitude for the IT Pro

http://blogs.ittoolbox.com/pm/career

http://techcareerconsult.blogspot.com

 

[MajP]

Change the 0 in parent id to to its actual id. Build a query linking this table to itself left join OrdertItem_Uno to Parent_cd. Sort on A.OrderItem_uno then by B.OrderItem_uno.

 

[MajP]

Actually you can just leave the 0 if you use a left outer join.

 

[mmorancbt]

MajP,

Thanks. I'll play around with this later today and let you know how it turns out.

Matthew Moran (career blog and podcast below)
Career Advice with Attitude for the IT Pro

http://blogs.ittoolbox.com/pm/career

http://techcareerconsult.blogspot.com

 

[PHV]

And what about this (SQL code) ?
ORDER BY IIf(parent_cd=0,OrderItem_uno,parent_cd), parent_cd


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mmorancbt]

That is the direction I was going. I used the iif statement in Access to create a concatenated field and sorted on it.

Iif(parent_cd<>0,parent_cd,orderitem_uno) & "-" & parent_cd as OrderItems

I can then sort on orderitems.

However, my database is SQL on the backend- no iif statement for transact sql. I did the same with the case statement.

Thanks.

Matthew Moran (career blog and podcast below)
Career Advice with Attitude for the IT Pro

http://blogs.ittoolbox.com/pm/career

http://techcareerconsult.blogspot.com