AndrewJen
asked on
Count subtotal bands in a VFP 9 report
Is there a way to get the number of subtotal bands in a Foxpro report?
In my specific instance, I need to find out how many distinct families a teacher as well as how many children she has, where there may be more than one child from each family being served by the same teacher...
For example:
Teacher A
Joe Smith
John Doe
Jill Doe
3 students
2 families
I'm trying to get my data prepared as follows; the question is - how can I get a count of the number of families served by the teacher.
CLOSE TABLES all
SET SAFETY OFF
SELECT * FROM cases,teacher WHERE cases.cidinte = teacher.cidinte AND status = "A" ORDER BY fullname INTO table SYS(2023)+"\inter_case"
SELECT 0
USE children ORDER cidchil
SELECT 0
USE family ORDER cidfami
SELECT inter_case
SET RELATION TO cidchil INTO children
SELECT children
SET RELATION TO cidfami INTO family
SELECT inter_case
INDEX on fullname + family.familyname + children.fname TO SYS(2023)+"\inter_case"
GO TOP
BROWSE FIELDS inter_case.fullname,family .familynam e,children .fname,chi ldren.lnam e,inter_ca se.date_as sig
In my specific instance, I need to find out how many distinct families a teacher as well as how many children she has, where there may be more than one child from each family being served by the same teacher...
For example:
Teacher A
Joe Smith
John Doe
Jill Doe
3 students
2 families
I'm trying to get my data prepared as follows; the question is - how can I get a count of the number of families served by the teacher.
CLOSE TABLES all
SET SAFETY OFF
SELECT * FROM cases,teacher WHERE cases.cidinte = teacher.cidinte AND status = "A" ORDER BY fullname INTO table SYS(2023)+"\inter_case"
SELECT 0
USE children ORDER cidchil
SELECT 0
USE family ORDER cidfami
SELECT inter_case
SET RELATION TO cidchil INTO children
SELECT children
SET RELATION TO cidfami INTO family
SELECT inter_case
INDEX on fullname + family.familyname + children.fname TO SYS(2023)+"\inter_case"
GO TOP
BROWSE FIELDS inter_case.fullname,family
The above "YourTable" must contain all childrens and their appropriate teachers.
ASKER
Thanks for the tip, I'm trying the following and getting an error "Group by Clause is missing or invalid"
SELECT fullname,COUNT(distinct(fa milyname)) ,children. fname,chil dren.lname ,cases.sta tus FROM intervent,family,children, cases WHERE intervent.cidinte = cases.cidinte ;
AND children.cidchil = cases.cidchil .and. family.cidfami = children.cidfami GROUP BY 1
SELECT fullname,COUNT(distinct(fa
AND children.cidchil = cases.cidchil .and. family.cidfami = children.cidfami GROUP BY 1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT Teacher, COUNT(DISTINCT Family) AS Families, COUNT(*) AS Childrens
FROM YourTable
GROUP BY 1
INTO CURSOR cResult
Now you may index the result on Teacher and use it in the report.