Thanks Jason. My main problem is having to type in all the fields in the Invoice table (almost 50 fields) since I need to return all the fields to stuff it into my VB Invoice object.
It's not the work involved in typing in each field in the SELECT and in the GROUP BY clause I'm worried about but having to modify this query if any fields are added/dropped from the Invoice table. The query will be hard-coded in a VB data-access component so I would have to recompile it if/when this happens.
I guess I could use stored procedure and call it from my component which will make it easier to maintain.
>I think you need something like the following:
>
>
>SELECT Invoice.Invoice_ID, Invoice.Invoice_Contact, Count(InvoiceLineItems.InvoiceLineItemsID) AS CountOfInvoiceLineItemsID
>FROM (Invoice INNER JOIN InvoiceLineItems ON Invoice.Invoice_ID = InvoiceLineItems.InvoiceID) LEFT JOIN InvoicePayments ON Invoice.Invoice_ID = InvoicePayments.InvoiceID
>GROUP BY Invoice.Invoice_ID, Invoice.Invoice_Contact, InvoicePayments.InvoicePaymentsID
>HAVING (((Count(InvoiceLineItems.InvoiceLineItemsID))>=3) AND ((InvoicePayments.InvoicePaymentsID) Is Null));
>
>
>The Is Null forces the return of only invoices where there is no payment records beacuse of the outer join.
It's "my" world. You're just living in it.