Using C# and SQL Server.

I have a table that stores information regarding shipments. In the table I have a column, ShipmentNumber, to help identify which assets went with which shipment.

Short Version:

For simplicity's sake here's what I need it to do:
Search by territory, sort by date, collect asset's from most recent shipment (assets with same shipment number) and display them. Have a next and previous arrow button to show assets from the next most recent shipment or previous most recent shipment.

Long Version:

My goal here is to be able to search the table by the territory number (location of where assets are shipped to) and have it somehow display the group of assets that were shipped with unique shipments.

For example, the user can ship 3 items to territory 509. For those 3 items the shipment number is 10. On the same day, the user ships 5 more items to territory 509 with shipment number 11.

The idea is to have a textbox where the user enters the territory, presses a search button and based on the most recent shipment, all the data appears on the form, however, I'd like only the assets from the most recent shipment to appear instead of every asset shipped to the territory being searched.

It would be much easier if the user knew the shipment number, but for the sake of being more user friendly, I've assumed the user does not know the shipment number and would just like to search shipments based on the destination (territory).

I'm beginning to think my tables have not be setup properly to design this type of search which is why I am here. I somewhat get the concept of Orders & Products but for some reason I can't wrap my mind on how to interpret that into what I need for shipping.

Any feedback, help, tips are much appreciated.

Joel

Ok to you give you a better idea, I've wrapped my head around it somewhat but still need some guidance.

I have my tables setup with the same concept as the orders/products relationship. Select an order, display the products.

My tables are Shipments & ShipmentItems. Select a Shipment, display the Shipment Items. Both tables have the field ShipmentNumber.

My trouble is with the query(s) that can make this happen.