You'd have to tell the view to update the scripts table.

>Mike,
>
>Yes, that would work, but can could use that as the the SQL record source for a grid?
>
>Cesar
>
>>Hi Cesar
>>
>>How about do the counting and the group by in a first step and join that with the other scripts fields in a second step?
>>
>>

SELECT Scripts.sc_id, COUNT(Phrases.ph_script_id) AS CountPhrases 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_id INTO CURSOR STEP1

>>
>>

SELECT STEP1.sc_id, STEP1.CountPhrases, Scripts.sc_code, ..., Scripts.sc_comments FROM STEP1 INNER JOIN data!scripts ON STEP1.sc_id = Scripts.sc_id

>>
>>>Sorry, sc_code is unique and that is why I know my comment record goes with that sc_code. If I take the GROUP BY out of this statement, I always get one record back under 70 behaviour which is not what I want. Under 80 behavior I actually get3 an error message that the GROUP BY clause is missing.
>>>
>>>Cesar
>>>
>>>>I'm a bit confused here. If sc_code is not unique, then how can you be sure that the sc_code and the sc_comments are from the same record? The only way you can actually be certain, as far as I can see, is if the sc_code is unique. If sc_code is unique, then you wouldn't really need the group by, since the count will always produce 1 anyway?
>>>>
>>>>If you group by sc_code, then what you get is basically a 'summary' that isn't necessarily any particular record, and if the memo field is not in the group by, then it can also be any record at all from the group (normally the last one).
>>>>
>>>>Are the comments simply repeated for each record instance of sc_code in scripts? If not, then I'm unsure what you mean about the comments being the correct comments for the sc_code.
>>>>
>>>>Why are memo fields not included? I can't really answer that, but it seems reasonable if you assume that memo fields are normally used for free form note type of information that would essentially make all the group by's that include it into single record groups.
>>>>
>>>>I think all you can do is to do the 'group by' by the book, and then go back and match up the comments record with the sc_code record that is returned (more than one select statement).
>>>>
>>>>Alan
>>>>
>>>>>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?