Sergey --

I'm not sure that I said I could use GROUP BY on all fields, and I certainly didn't mean to. Since I'm doing SUM(AMT), certainly I couldn't group by the AMT field, could I?

The first time I stumbled over this problem, I had about 15 fields that were always identical for each occurence of "OrdNbr" (so these should have been in one table), and about 6 more that were really detail items (part, quantity, price, etc.) that should have been in a separate table. My ultimate goal is to get a table with all the 15 fields that belong in an "Order table" (one record per unique OrdNbr), along with one or more fields that are a summary from what should have been in the detail table.

My apologies if my posts did not make this clear.

Jim

P.S.: As you've noted quite recently, it's almost impossible to pose a question in this forum and provide too much information about it. Being aware of that, I tried to be as explicit as possible in my post. Reminds me of an old algorithm for estimating the time to do a project ... take your best possible guess on how long it will take, and add 25%. As you can see, having done this, you now have a new best possible guess, to which you have to add ....

>Yes, they will but in the original post you said that you could use GROUP BY on all fields to get desired result. So which statement is correct?
>
>>Does it? I mean, if there is a field Orders.Partno, and there are records for one OrdNbr that have different PartNos, won't those still come out as distinct records?
>>
>>Jim
>>
>>>If you don't have memo fileds in the table, following should work

>>>SELECT DIST * FROM (
>>>  SELECT Orders.*, tot.TOTAMT ;
>>>    FROM Orders ;
>>>      JOIN (SELECT OrdNbr, SUM(AMT) AS TOTAMT FROM Orders GROUP BY OrdNbr) tot ;
>>>    ON tot.OrdNbr = Orders.OrdNbr
>>>) dt1
>>>