Crazy Sorting

[Johnny Starks]

Hi Everybody,

What I have is a query of products, each product has a
sort order number assigned to it, i.e 14507880. On all
queries and reports in the application they will be sorted
by that number. When a product is added to an order, it is
given a item no, this is manully entered by the Order
Entry. i.e 1, 2, 3 so on and so on. There is a category of
products called Accessories which are "attached" to a
product. They will be given an item no like 1a, 1b, 2a.
Those accessories will also have a sort order, but they
will be different then that of product they are attached
to. What I need the query to do is sort by the sort order
for the product, unless it is an accessory then I need it
to sort by the item no. Does it make any sense, I can
clarify if anybody can help!

thanks johnny

 

[Johny Starks]

We have tblOrders with a one to many relationship with
tblOrderDetails. Then we have tblProducts with a one to
many relationship with tblOrderDetails. The sortOrder is
on tblProducts while the item number is in tblOrderDetails.

I guess the logic is an Order can have many Order Details
(line items) while each line item has a product.