simple Query, using NZ, is Double-Counting Revenue Numbers

[ryguy7272]

This is one field in my query:
Revenue-Class 1:
Sum(nz([qryMTD_WandaClass1]![SumOfCUR_MTD_REV],0)+nz([qryMTD_WandaClass1]![SumOfOCT],0))
Total: Expression

All I want to do is sum items for sales reps in both: [SumOfCUR_MTD_REV] &
[SumOfOCT]

It is double-counting my revenues, but I am not sure why. Can someone
please explain?


Thanks,
Ryan---

 

[KARL DEWEY]

Here is a guess.
My guess is that [qryMTD_WandaClass1] is a query with two tables that are
not joined therefore creating a cartesian effect.

 

[ryguy7272]

Profound; I hadn't though of that. Here is the SQL for [qryMTD_WandaClass1]:
SELECT tblRVP_Mapping.RVP, tblRVP_Mapping.Director, tblRVP_Mapping.[Sales
Rep], Sum(tblWandaRegion2008.CUR_MTD_REV) AS SumOfCUR_MTD_REV,
Sum(tblWandaRegion2008.OCT) AS SumOfOCT, Sum(tblWandaRegion2008.NOV) AS
SumOfNOV, tblWandaRegion2008.inventoryClass
FROM tblRVP_Mapping INNER JOIN tblWandaRegion2008 ON tblRVP_Mapping.[Sales
Rep] = tblWandaRegion2008.SalesRep
GROUP BY tblRVP_Mapping.RVP, tblRVP_Mapping.Director, tblRVP_Mapping.[Sales
Rep], tblWandaRegion2008.inventoryClass
HAVING (((tblWandaRegion2008.inventoryClass)="Class1"));

I'm simply using an Inner Join line there. I am linking Sales Reps in a
'Mapping' table to Sales Reps in another table with all Reps and sales
figures. I don't think that is the problem. What do you think Karl?


Thanks,
Ryan---

--
RyGuy

Here is a guess.
My guess is that [qryMTD_WandaClass1] is a query with two tables that are
not joined therefore creating a cartesian effect.
--
KARL DEWEY
Build a little - Test a little

This is one field in my query:
Revenue-Class 1:
Sum(nz([qryMTD_WandaClass1]![SumOfCUR_MTD_REV],0)+nz([qryMTD_WandaClass1]![SumOfOCT],0))
Total: Expression

All I want to do is sum items for sales reps in both: [SumOfCUR_MTD_REV] &
[SumOfOCT]

It is double-counting my revenues, but I am not sure why. Can someone
please explain?


Thanks,
Ryan---

 

[ryguy7272]

If I use this:
Avg(nz([qryMTD_WandaClass1]![SumOfCUR_MTD_REV],0)+nz([qryMTD_WandaClass1]![SumOfOCT],0))

It works!! However, why would I have to use an average to get a sum?
Something is not right here...

TIA,
Ryan---

--
RyGuy

Profound; I hadn't though of that. Here is the SQL for [qryMTD_WandaClass1]:
SELECT tblRVP_Mapping.RVP, tblRVP_Mapping.Director, tblRVP_Mapping.[Sales
Rep], Sum(tblWandaRegion2008.CUR_MTD_REV) AS SumOfCUR_MTD_REV,
Sum(tblWandaRegion2008.OCT) AS SumOfOCT, Sum(tblWandaRegion2008.NOV) AS
SumOfNOV, tblWandaRegion2008.inventoryClass
FROM tblRVP_Mapping INNER JOIN tblWandaRegion2008 ON tblRVP_Mapping.[Sales
Rep] = tblWandaRegion2008.SalesRep
GROUP BY tblRVP_Mapping.RVP, tblRVP_Mapping.Director, tblRVP_Mapping.[Sales
Rep], tblWandaRegion2008.inventoryClass
HAVING (((tblWandaRegion2008.inventoryClass)="Class1"));

I'm simply using an Inner Join line there. I am linking Sales Reps in a
'Mapping' table to Sales Reps in another table with all Reps and sales
figures. I don't think that is the problem. What do you think Karl?


Thanks,
Ryan---

--
RyGuy

Here is a guess.
My guess is that [qryMTD_WandaClass1] is a query with two tables that are
not joined therefore creating a cartesian effect.
--
KARL DEWEY
Build a little - Test a little

This is one field in my query:
Revenue-Class 1:
Sum(nz([qryMTD_WandaClass1]![SumOfCUR_MTD_REV],0)+nz([qryMTD_WandaClass1]![SumOfOCT],0))
Total: Expression

All I want to do is sum items for sales reps in both: [SumOfCUR_MTD_REV] &
[SumOfOCT]

It is double-counting my revenues, but I am not sure why. Can someone
please explain?


Thanks,
Ryan---

 

[KARL DEWEY]

Why do you have -- Sum(tblWandaRegion2008.NOV) AS SumOfNOV and
tblWandaRegion2008.inventoryClass?

Why group by tblWandaRegion2008.inventoryClass when you will only have
Class1 listed?

Have you checked the data produced by qryMTD_WandaClass1? That is probably
where it is doubled. Start taking the query apart until you find the problem.
--
KARL DEWEY
Build a little - Test a little

Profound; I hadn't though of that. Here is the SQL for [qryMTD_WandaClass1]:
SELECT tblRVP_Mapping.RVP, tblRVP_Mapping.Director, tblRVP_Mapping.[Sales
Rep], Sum(tblWandaRegion2008.CUR_MTD_REV) AS SumOfCUR_MTD_REV,
Sum(tblWandaRegion2008.OCT) AS SumOfOCT, Sum(tblWandaRegion2008.NOV) AS
SumOfNOV, tblWandaRegion2008.inventoryClass
FROM tblRVP_Mapping INNER JOIN tblWandaRegion2008 ON tblRVP_Mapping.[Sales
Rep] = tblWandaRegion2008.SalesRep
GROUP BY tblRVP_Mapping.RVP, tblRVP_Mapping.Director, tblRVP_Mapping.[Sales
Rep], tblWandaRegion2008.inventoryClass
HAVING (((tblWandaRegion2008.inventoryClass)="Class1"));

I'm simply using an Inner Join line there. I am linking Sales Reps in a
'Mapping' table to Sales Reps in another table with all Reps and sales
figures. I don't think that is the problem. What do you think Karl?


Thanks,
Ryan---

--
RyGuy

Here is a guess.
My guess is that [qryMTD_WandaClass1] is a query with two tables that are
not joined therefore creating a cartesian effect.
--
KARL DEWEY
Build a little - Test a little

This is one field in my query:
Revenue-Class 1:
Sum(nz([qryMTD_WandaClass1]![SumOfCUR_MTD_REV],0)+nz([qryMTD_WandaClass1]![SumOfOCT],0))
Total: Expression

All I want to do is sum items for sales reps in both: [SumOfCUR_MTD_REV] &
[SumOfOCT]

It is double-counting my revenues, but I am not sure why. Can someone
please explain?


Thanks,
Ryan---

 

[ryguy7272]

I apologize for the extremely-delayed-follow-up. I thought I responded, but
as I am just now reading some old posts, I realize that I didn't have closure
here. As it turned out, I was doing a Sum operation in the
'qryMTD_WandaClass1' and then I created the following:
Revenue-Class 1
Sum(nz([qryMTD_WandaClass1]![SumOfCUR_MTD_REV],0)+nz([qryMTD_WandaClass1]![SumOfOCT],0))

Silly, but true. Two sums is one two many.

Thanks Karl for the advice!!

Regards,
Ryan---



--
RyGuy

Why do you have -- Sum(tblWandaRegion2008.NOV) AS SumOfNOV and
tblWandaRegion2008.inventoryClass?

Why group by tblWandaRegion2008.inventoryClass when you will only have
Class1 listed?

Have you checked the data produced by qryMTD_WandaClass1? That is probably
where it is doubled. Start taking the query apart until you find the problem.
--
KARL DEWEY
Build a little - Test a little

Profound; I hadn't though of that. Here is the SQL for [qryMTD_WandaClass1]:
SELECT tblRVP_Mapping.RVP, tblRVP_Mapping.Director, tblRVP_Mapping.[Sales
Rep], Sum(tblWandaRegion2008.CUR_MTD_REV) AS SumOfCUR_MTD_REV,
Sum(tblWandaRegion2008.OCT) AS SumOfOCT, Sum(tblWandaRegion2008.NOV) AS
SumOfNOV, tblWandaRegion2008.inventoryClass
FROM tblRVP_Mapping INNER JOIN tblWandaRegion2008 ON tblRVP_Mapping.[Sales
Rep] = tblWandaRegion2008.SalesRep
GROUP BY tblRVP_Mapping.RVP, tblRVP_Mapping.Director, tblRVP_Mapping.[Sales
Rep], tblWandaRegion2008.inventoryClass
HAVING (((tblWandaRegion2008.inventoryClass)="Class1"));

I'm simply using an Inner Join line there. I am linking Sales Reps in a
'Mapping' table to Sales Reps in another table with all Reps and sales
figures. I don't think that is the problem. What do you think Karl?


Thanks,
Ryan---

--
RyGuy

Here is a guess.
My guess is that [qryMTD_WandaClass1] is a query with two tables that are
not joined therefore creating a cartesian effect.
--
KARL DEWEY
Build a little - Test a little


:

This is one field in my query:
Revenue-Class 1:
Sum(nz([qryMTD_WandaClass1]![SumOfCUR_MTD_REV],0)+nz([qryMTD_WandaClass1]![SumOfOCT],0))
Total: Expression

All I want to do is sum items for sales reps in both: [SumOfCUR_MTD_REV] &
[SumOfOCT]

It is double-counting my revenues, but I am not sure why. Can someone
please explain?


Thanks,
Ryan---