Query with inconsistent results

  • Thread starter Thread starter Randal
  • Start date Start date
R

Randal

I have a query that produces different results each time it is executed.
There are 2 tables joined by two fields - code and code sub. The query is
grouped on field 1 from table 1 and summing the contents of field 1 from
table 2. Contents of the tables are not changing. Any help is
appreciated.
 
I __SUSPECT__ that the Summing of the Contents of Field 1 in table 2 is giving
you inconsistent results in the decimal portion of the number. You might be
able to get around the problem by forcing a set number of digits (rounding).

Can you post the SQL of your queries and descriptions of the linking fields
(text, number of type Double)? Someone may be able to spot what is happening.
 
Actually the totals vary sometimes by several thousand $$. I have changed
the query to give detail lines instead of sums and the # of records varies
each time it is executed. Query is run of linked VFP .dbf files.

SELECT Table1.flag1, Sum([Table2].amt1) AS SumOfamt1, Sum([Table2].amt2) AS
SumOfamt2, Sum([Table2].amt3) AS SumOfamt3, Sum([Table2].amt4) AS SumOfamt4,
Sum([Table2].amt5) AS SumOfamt5
FROM Table1 INNER JOIN Table2 ON (Table1.code_sub = [Table2].company_sb) AND
(Table1.code = [Table2].company)
WHERE (((Table1.flag2) Not Like "C"))
GROUP BY Table1.flag1;
 
Sorry, I can't help you. Query look fine, but I've not worked with VFP .dbf
files and have no way to see if there is an error in the ISAM linking the tables
to your Access frontend.
Actually the totals vary sometimes by several thousand $$. I have changed
the query to give detail lines instead of sums and the # of records varies
each time it is executed. Query is run of linked VFP .dbf files.

SELECT Table1.flag1, Sum([Table2].amt1) AS SumOfamt1, Sum([Table2].amt2) AS
SumOfamt2, Sum([Table2].amt3) AS SumOfamt3, Sum([Table2].amt4) AS SumOfamt4,
Sum([Table2].amt5) AS SumOfamt5
FROM Table1 INNER JOIN Table2 ON (Table1.code_sub = [Table2].company_sb) AND
(Table1.code = [Table2].company)
WHERE (((Table1.flag2) Not Like "C"))
GROUP BY Table1.flag1;

John Spencer (MVP) said:
I __SUSPECT__ that the Summing of the Contents of Field 1 in table 2 is giving
you inconsistent results in the decimal portion of the number. You might be
able to get around the problem by forcing a set number of digits (rounding).

Can you post the SQL of your queries and descriptions of the linking fields
(text, number of type Double)? Someone may be able to spot what is happening.
Click to expand...
Click to expand...
 
Randal said:
Actually the totals vary sometimes by several thousand $$. I have changed
the query to give detail lines instead of sums and the # of records varies
each time it is executed. Query is run of linked VFP .dbf files.
Click to expand...
Hi Randal,

A year or so back, Arno had a similar problem (VFP)
and this was what corrected it:

**** QUOTE ******
- problem solved -

i found the problem in the odbc settings for visula foxpro databases. the
standard value for "BackgroundFetch" was set to "YES". when i set it to "NO"
all the results where ok!

i changed all the settings in the odbc-connection string but
"BackgroungFetch" was the one that solved the problem.

thank you for your help

arno
**** UNQUOTE ****
 
Great! Thanks! This was the answer.

Gary Walter said:
Hi Randal,

A year or so back, Arno had a similar problem (VFP)
and this was what corrected it:

**** QUOTE ******
- problem solved -

i found the problem in the odbc settings for visula foxpro databases. the
standard value for "BackgroundFetch" was set to "YES". when i set it to "NO"
all the results where ok!

i changed all the settings in the odbc-connection string but
"BackgroungFetch" was the one that solved the problem.

thank you for your help

arno
**** UNQUOTE ****
Click to expand...
 
Back
Top