HELP!!! SUM of Query changes and is different

[wiredog]

Someone please help. I have been staring and rerunning versions of my
query for the last 5 days and I can not understand why the SUM
changes.

I have two tables. One table titled "Step 2 - Market Names1" contains
the following columns--
SWITCH, NPA, NXX, MOU

The second is titled "LERG 6" and contains several columns but have,
NPA, NXX in common with my first table.

When I run the following query--

SELECT [STEP 2 - MARKET NAME1].Switch, Sum([STEP 2 - MARKET
NAME1].MOU) AS SumOfMOU
FROM [STEP 2 - MARKET NAME1]
GROUP BY [STEP 2 - MARKET NAME1].Switch;

.. . .on table one to SUM the minutes of use (MOU), I get values for
the total MOUs by SWITCH

However when I run this query--

SELECT [STEP 2 - MARKET NAME1].Switch, Sum([STEP 2 - MARKET
NAME1].MOU) AS SumOfMOU
FROM [STEP 2 - MARKET NAME1] LEFT JOIN [LERG 6] ON ([STEP 2 - MARKET
NAME1].NPA = [LERG 6].NPA) AND ([STEP 2 - MARKET NAME1].NXX = [LERG
6].NXX)
GROUP BY [STEP 2 - MARKET NAME1].Switch;

or this

SELECT [STEP 2 - MARKET NAME1].Switch, [STEP 2 - MARKET NAME1].[MARKET
NAME], [LERG 6].[LOC STATE], Sum([STEP 2 - MARKET NAME1].MOU) AS
SumOfMOU
FROM [STEP 2 - MARKET NAME1] LEFT JOIN [LERG 6] ON ([STEP 2 - MARKET
NAME1].NPA = [LERG 6].NPA) AND ([STEP 2 - MARKET NAME1].NXX = [LERG
6].NXX)
GROUP BY [STEP 2 - MARKET NAME1].Switch, [STEP 2 - MARKET
NAME1].[MARKET NAME], [LERG 6].[LOC STATE];


.. . .the SUM for the MOU's increase! Why does the sum change? I
believe I should still be seeing the same SUM as I did when I ran the
first query.

I am not adding or deleting any entries and their related MOU. I am
only establishing a relationship between the two tables or adding
additional columns to view on the results. I have tried changing and
combining the Joins (LEFT, RIGHT, INNER) as well DISTINCT, DISTINCTROW
but I can not obtain identical SUM's from both queries despite coming
form the same base data.

I am ready for the nuthouse!! Please tell me what I need to do to
make sure I get this right.

TIA,
Ben

 

[Michel Walsh]

Hi,



It is quite probable that the join generates duplicated records.


Remove the SUM and the GROUP BY clause, in any of the last two queries, and
you should see that some record from the table [STEP 2 ... ] are
duplicated (from the original), or the record number is not the same. The
SUM sees those duplicated values, and sum them, making a different result
than if there is no such dup.


Your work would be to eliminate logically the duplicated values (there is no
magic, you have to rework the logic, or find the "erroneous data", or to
rethink about the approach, ... )



Hoping it may help,
Vanderghast, Access MVP