Solved

Count subtotal bands in a VFP 9 report

Posted on 2011-09-26
4
685 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 41

Expert Comment

by:pcelba
Comment Utility
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 41

Expert Comment

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

Author Comment

by:AndrewJen
Comment Utility
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 41

Accepted Solution

by:
pcelba earned 500 total points
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
How to fix orphan DBF 9 634
Data Import from a CSV file 8 485
FORM caption height 4 180
MS Access no longer ablle to link to FoxPro tables 30 290
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…
In this article, you will read about the trends across the human resources departments for the upcoming year. Some of them include improving employee experience, adopting new technologies, using HR software to its full extent, and integrating artifi…
This video discusses moving either the default database or any database to a new volume.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now