More records in child table

[Toman]

Hi

I have more records in child table that correspond to a record in parent. It must be easy to count them or get a sum of theirs numeric fields, but I can't find how.
Will someone help me? I hope I don't decrease the level of this forum so much with this question.

Thank you in advance
Tom

 

[kueyiar]

It depends on hoy you want to count/sum , if you are trying to do it inside a sql statement it would be something like this:

STATEMENT FOR COUNT:
SELECT COUNT(*) AS TOTAL, [FIELD1], [FIELD2] FROM TABLE WHERE [YOUR CONDITION] GROUP [BY COMMON FIELD]

STATEMENT FOR SUM:
SELECT SUM([FIELD]) AS TOTAL, [FIELD1], [FIELD2] FROM TABLE WHERE [YOUR CONDITION] GROUP [BY COMMON FIELD]

And don't worry, it takes practice, and we've all been there [simle2]


antonio

 

[ramani]

HI Tom

SET ENGINEBEHAVIOR 70 && if VFP 8.0

SELECT master.field1, master.field2....., ;
SUM(child.field1) AS sum1, ;
SUM(child.field2) AS sum2, ;
COUNT('child') AS myCount ;
FROM master, child ;
WHERE master.keyField == child.Keyfield ;
GROUP BY master.keyfield



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[Toman]

Hi
I knew I haven't said all. My relation is not quit standard. I use SET EXACT OFF purposely so as to get more records in child then with clause WHERE master.keyField == child.Keyfield. in SQL

For example:
if master.keyField = "black"
in child I receive records with fields
"black"
"black and white"
"black power"
and this is exactly what I need and want.

If only SQL can work in my case, how could I code it to get results just described?
Thank you and I'm sorry to trouble you.
Tom

 

[Toman]

HURRAAAH, I did it !!!!!!

Thank you [red]R[/red][blue]a[/blue][olive]m[/olive][navy]a[/navy][green]n[/green][fuchsia]i.[/fuchsia] (Colors are instead of star)

Now it's completely clear to me. I only had to modify your WHERE condition slightly to fit my needs from:

WHERE master.keyField == child.Keyfield
to
WHERE ATC(ALLTRIM(master.keyField), child.Keyfield) = 1

and it works perfectly even on VFP5.
I believe, that you would directly suggest this, if I've had explained my problem more precisely from the beginning. (English?! , sorry)
I'm so happy now...

Tom