Jay and Alan,

The reasons for doing my query are very different than the customers with accounts example shown above. Here is what I was doing in VFP 6 and why I need to return the MEMO field as part of a query that has a GROUP BY.

My one to many relationship deals with scripts and phrases. A script contains a bunch of phrases. I am displaying a grid that shows each script with the number of phrases (a COUNT) for each script. So, what I have is:

SELECT Scripts.sc_code, ..., Scripts.sc_comments, COUNT(Phrases.ph_script_id) FROM data!scripts LEFT OUTER JOIN data!phrases ON Scripts.sc_id = Phrases.ph_script_id WHERE Scripts.sc_project == ?parentpkvalue GROUP BY Scripts.sc_code

So, the reason for the GROUP BY is so I can count the number of phrases that belong in a script. The sc_comments field is the MEMO field which I want to display in my grid. Since I am grouping by scripts (sc_code) I know that my comments field is the correct one for that script. Is there any better way to do this, or is my need to use a MEMO with a GROUP BY justified?

Thanks.

Cesar

>Cesar --
>
>Alan has responded to this in several message, so I won't go into the SQL issues.
>
>You're finding the customers that have accounts, I assume.
>
>Is the memo related to the customer? If so, then perhaps your data design would benefit from a customer table with a key into the accounts table. The customer table would maintain this memo information and would be able to report it with a join condition to your query on the account table.
>
>I wouldn't assume that you'd be querying a memo related to the account, since your concern is customers, but perhaps that is the case.
>
>Some thoughts...
>
> Jay
>
>>What would you do if you had a MEMO field as part of the non aggregate statement in VFP7. I can't return MAX of that. Nor can I add that to the GROUP BY statement. Is there any other way to make it aggregate?