Sorry, of course you can't do that...

Have you tried to an explicit join? I can't see why your query is crashing, maybe you're forced to SET ENGINEBEHAVIOR.

SELECT invoice.nInvoiceNumber, invoice.dInvoiceDate,
SUM(invoicedetail.nQuantity*invoicedetail.nUnitPrice) AS nTotal
FROM Invoice ;
    join InvoiceDetail on (InvoiceDetail.nParentID = invoice.nId)
GROUP BY invoice.nInvoiceNumber, invoice.dInvoiceDate Order BY nInvoiceNumber

>You cannot do that either because you cannot group by an aggregate field.
>This is really bugging me .....
>-Mark
>
>>As a test, add nTotal to the group by clause...it won't give you the result you want, but I want to see if it eliminates the error.
>>
>>>No same thing.
>>>-Mark
>>>
>>>>Have you tried (it still may not work because of sum of multiplied fields?):
>>>>
>>>>

>>>>SELECT invoice.nInvoiceNumber, invoice.dInvoiceDate,
>>>>SUM(invoicedetail.nQuantity*invoicedetail.nUnitPrice) AS nTotal
>>>>FROM Invoice, InvoiceDetail
>>>>WHERE Invoice.nID = InvoiceDetail.nParentID
>>>>GROUP BY nInvoiceNumber, dInvoiceDate Order BY nInvoiceNumber
>>>>

>>>>
>>>>>What is wrong???????? I know I can bypass the problem with the SET ENGINEBEHAVIOUR 70, but I would really like to know what the correct syntax should be.
>>>>>
>>>>>TIA