>I am doing a multi table view on two tables that have a one to many
>relationship. I want to retrieve only records from the parent table that
>have greater than a certain amount of records in the child table.
>
>For example: I want to see from my "Clients" table only those clients who
>have more than 50 invoices in my "Invoice" table and are in the state of
>Arkansas.
>
>I have experimented with SQL statements with no success. Any suggestions
>will be very much appreciated. Thanks.
Elgin, try this:
SELECT Client.ClientID, client.state, cnt(Invoices.ClientID) as NumInv;
FROM Client, Invoices;
WHERE client.clientid = invoices.clientid;
AND client.state = 'CA';
into cursor CurTemp;
Group by ClientID having NumInv > 50
Of course, I used California since I couldn't remember the abbreviation for Arkansas :-)
Anyway, check out the HAVING clause. It's really useful.
HTH
Barbara