PRB: Query to a Report Causes an Inaccurate Sum or Count (120187)
The information in this article applies to:
- Microsoft Visual FoxPro for Windows 3.0
- Microsoft FoxPro for MS-DOS 2.0
- Microsoft FoxPro for MS-DOS 2.6
- Microsoft FoxPro for MS-DOS 2.6a
- Microsoft FoxPro for Windows 2.6
- Microsoft FoxPro for Windows 2.6a
- Microsoft FoxPro for UNIX 2.6
This article was previously published under Q120187 SYMPTOMS
When you are running a query to a report using two or more databases with a
one-to-many relationship and the report is counting or performing other
math functions on fields from the parent database, the count or value from
the math functions is inaccurate.
RESOLUTION
To get an accurate count, you need to create a user-defined function (UDF).
For an example of a UDF that produces the correct count, see "Workaround"
in the "More Information" section below.
WORKAROUND
To obtain the correct count, you can use the following UDF.
FUNCTION mycount
PARAMETERS x
recnum = RECNO()
mygroup = state
SELECT DISTINCT customer.cno, customer.state;
FROM customer, invoices;
WHERE invoices.cno = customer.cno;
AND customer.state = mygroup;
INTO CURSOR counter
COUNT TO x
SELECT QUERY
GO recnum
RETURN x
REFERENCES
FoxPro for Windows "Getting Started," Chapter 9
FoxPro for Windows and MS-DOS "Language Reference," page L3-1114
Modification Type: | Major | Last Reviewed: | 12/3/2003 |
---|
Keywords: | kbcode KB120187 |
---|
|