[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Count subtotal bands in a VFP 9 report

Posted on 2011-09-26
4
Medium Priority
?
766 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 43

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 43

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 43

Accepted Solution

by:
pcelba earned 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Geo-targeting is the practice of distributing content based on a person’s location, as best as you can determine it. Let’s look at some ways you could successfully use this tactic. The following tips and case studies could lead to meaningful results.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

650 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