Sum Function Inconsistency

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello-

I'm getting inconsistent results from the following query:

SELECT tblMeasure.Job_ID, tblMeasure.Measure_Offset,
CSng(Sum(IIf([New_Fixture_Code]="N/C" Or
IsNull([tblMeasure]![Old_Fixture_Qty]) Or
IsNull([tblMeasure]![Fixture_Hrs]),0,IIf(IsNull([tlkpFixtureOld].[Fixture_Code]),0,(([tblMeasure]![Old_Fixture_Qty]*[tlkpFixtureOld]![Rated_Watts]*[tblMeasure]![Fixture_Hrs])*52)*0.001)))) AS Old_Watts
FROM (tblMeasure INNER JOIN tlkpFixture AS tlkpFixtureNew ON
tblMeasure.New_Fixture_Code = tlkpFixtureNew.Fixture_Code) LEFT JOIN
tlkpFixture AS tlkpFixtureOld ON tblMeasure.Old_Fixture_Code =
tlkpFixtureOld.Fixture_Code
GROUP BY tblMeasure.Job_ID, tblMeasure.Measure_Offset, tblMeasure.Change
HAVING (((tblMeasure.Job_ID)="HA030865") AND ((tblMeasure.Change)=False));

The data does not presently have multiple Job_ID/Measure_Offset records, so
the Sum function is extraneous, but may be needed in the future. The query
runs fine and gives correct results in Access 2000, 2002 or 2003 with the
data also in Access. However, when I link to SQL Server data, or even run the
query directly in SQL Server, Old_Watts comes out incorrect - the quantity is
four times the amount it should be. Has anyone run across anything like this?
 
Sometimes it helps to create a simple select query(one without the groups
and totals) that is going to be the basis of the group by. Run that simple
query and view the records. Compare them Access vs. SQL server. Maybe the
data is different, or perhaps SQL is behaving differently than Access.
(Known to happen as they don't work the same.)
 
Back
Top