Solved

Count subtotal bands in a VFP 9 report

Posted on 2011-09-26
4
730 Views
Last Modified: 2012-05-12
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.familyname,children.fname,children.lname,inter_case.date_assig

0
Comment
Question by:AndrewJen
  • 3
4 Comments
 
LVL 42

Expert Comment

by:pcelba
ID: 36598883
You are not disclosing your data model, so the solution is just a brief estimation:

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.
0
 
LVL 42

Expert Comment

by:pcelba
ID: 36598889
The above "YourTable" must contain all childrens and their appropriate teachers.
0
 
LVL 3

Author Comment

by:AndrewJen
ID: 36598947
Thanks for the tip, I'm trying the following and getting an error "Group by Clause is missing or invalid"


SELECT fullname,COUNT(distinct(familyname)),children.fname,children.lname,cases.status FROM intervent,family,children,cases WHERE intervent.cidinte = cases.cidinte ;
AND children.cidchil = cases.cidchil .and. family.cidfami = children.cidfami GROUP BY 1

0
 
LVL 42

Accepted Solution

by:
pcelba earned 500 total points
ID: 36599011
GROUP BY must be defined for all non-aggregated columns, so:

SELECT fullname, COUNT(distinct(familyname)), ;
       children.fname, children.lname, cases.status ;
  FROM intervent, family, children, cases ;
 WHERE intervent.cidinte = cases.cidinte ;
   AND children.cidchil = cases.cidchil ;
   AND family.cidfami = children.cidfami ;
 GROUP BY 1, 3, 4, 5

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: http://msdn.microsoft.com/en-us/vfoxpro/default.aspx (http://msd…
Direct mail marketing is the act of mailing materials straight to prospective customers. This wide form of marketing is one of the oldest methods of communicating with a geographic based demographic. So is it useful in 2017 and beyond?
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question