>I have a table that contains shipping and receiving info for parts. The first record is the shipping record. Subsequent records are receipt records:
>
>
>
>This part has been shipped from location 1, received at location 152, the moved to
>locations 565 and 202
>
>RecId PartNo LocationId SerialNo Qty Date Sequence
>
>5228 17908 1 HUB5K043NC 1 NULL 1
>5229 17908 152 HUB5K043NC 1 2009-01-22 00:00:00.000 2
>5230 17908 565 HUB5K043NC 1 2009-02-02 00:00:00.000 3
>5231 17908 202 HUB5K043NC 1 2009-02-02 00:00:00.000 4
>
>
>
>This part has been shipped from location 1, but not yet received
>
>RecId PartNo LocationId SerialNo Qty Date Sequence
>
>5953 16577 1 3BV0GW03 1 2009-04-01 00:00:00.000 1
>
>
>
>The combination of PartNo and SerialNo make a record unique. How do I pull only records that have not yet been received?
There is probably a way to do it, using a subquery, but I can't see what it is. I don't understand why the first record (recid 5228) has a null date field and the last (5953) has a non-null date. Don't they both record the shipment of a part from some location? Or why records 5229, 5230, and 5231 are shipments which have been received yet look just like record 5953, which has been shipped but not received. The data model is really making it tough for you. There should be separate dates, shipped date and received date, instead of a generic field called Date (not a good idea itself) which is called upon to do double duty. Personally I would store shipments and receipts in separate tables, which would also make the job straightforward.