Help With a Query; Using Expression Builder

[ryguy7272]

I’m suck on an Expression that should be pretty simple (I think). In the
Total line, I have Expression. In the Field line I have, “SUM:
Sum([tblRegion]![CUR_MTD_REV]+[tblRegion]![JUL])â€

Basically, I am trying to sum all the Current Month To Date revenue and the
July revenue, for all Sales Reps, but I only have to do this for Inventory
Class of type Class2.

I am getting some VERY bizarre results. For instance, one sales rep should
have about $48,000 and Access is telling me that the rep has $4.8 million.
Clearly something is wrong, but I can’t figure out what.

SQL:

SELECT tblRVP_Mapping.RVP, tblRVP_Mapping.Director, tblRVP_Mapping.[Sales
Rep], tblRegion.inventoryClass, tblRegion.CUR_MTD_REV, tblRegion.JUL,
Sum([tblRegion]![CUR_MTD_REV]+[tblRegion]![JUL]) AS [SUM]
FROM (tblRM_MediaSales2008 INNER JOIN tblRVP_Mapping ON
tblRM_MediaSales2008.SalesRep = tblRVP_Mapping.[Sales Rep]) INNER JOIN
tblRegion ON tblRVP_Mapping.[Sales Rep] = tblRegion.SalesRep
GROUP BY tblRVP_Mapping.RVP, tblRVP_Mapping.Director, tblRVP_Mapping.[Sales
Rep], tblRegion.inventoryClass, tblRegion.CUR_MTD_REV, tblRegion.JUL
HAVING (((tblRegion.inventoryClass)="Class2"));

If anyone can tell me what is wrong here I would really appreciate it.
Finally, I would like to sum a few more elements, but I think I can figure
out how to do that, as soon as I understand the required logic.

Thanks so much,
Ryan---

 

[Lord Kelvan]

“SUM: Sum([tblRegion]![CUR_MTD_REV]+[tblRegion]![JUL])”

that looks really weird to me first you shouldnt use key words as your
expression names to change SUM: to something else TOTAL: may be better
second your problem is most likly the

sum()

what it seems to be tyring to do is add

[tblRegion]![CUR_MTD_REV]+[tblRegion]![JUL]

then sum the entire column using the

sum()

what you should do it remove the sum() and just have

TOTAL: [tblRegion]![CUR_MTD_REV]+[tblRegion]![JUL]

but if you are trying to group all the values together for a person
then you should put the sum on

sum(tblRegion.CUR_MTD_REV) as sumofCUR_MTD_REV, sum(tblRegion.JUL) as
sumofjul

in your query and then go

TOTAL: sumofCUR_MTD_REV + sumofjul

 

[ryguy7272]

I'm sorry, but I don't understand that third line form the bottom.
sum(tblRegion.CUR_MTD_REV) as sumofCUR_MTD_REV, sum(tblRegion.JUL) as
sumofjul

I am pretty sure Access is not going to allow that in the QBE.

If I use the following SQL, Access gives me the correct values:
SELECT tblRVP_Mapping.[Sales Rep], tblRegion.inventoryClass, tblRegion.JUL,
tblRegion.CUR_MTD_REV
FROM (tblRM_MediaSales2008 INNER JOIN tblRVP_Mapping ON
tblRM_MediaSales2008.SalesRep = tblRVP_Mapping.[Sales Rep]) INNER JOIN
tblRegion ON tblRVP_Mapping.[Sales Rep] = tblRegion.SalesRep
GROUP BY tblRVP_Mapping.[Sales Rep], tblRegion.inventoryClass,
tblRegion.JUL, tblRegion.CUR_MTD_REV
HAVING (((tblRegion.inventoryClass)="Class2"));

The problem is that Access won't allow me to sum the sales for reps. As
soon as I change 'Group By' to 'Sum' on the QBE, on the Total line, things
get pretty crazy. On rep in particular goes from $13,314 to $4,050,382. I
am sure the rep would love to get commissions based on this, but that number
is just totally wrong. There is no apparent combination of values in the
entire table that sum to that amount. I just can't figure out how Access is
arriving at this kind of result.

Are there are other ideas out there?

Thanks,
Ryan---

 

[Lord Kelvan]

i am sorry that was sql

in that query builder you would have three fields

sumofCUR_MTD_REV: sum(tblRegion.CUR_MTD_REV)
sumofjul: sum(tblRegion.JUL)
and
TOTAL: sumofCUR_MTD_REV + sumofjul

my apologies i just took it formt he sql you pasted

 

[Lord Kelvan]

are you trying to get something like

Sales Rep inventoryClass JUL CUR_MTD_REV
rep1 class1 30 50
rep1 class1 10 30
rep1 class1 50 30
rep1 class2 50 40
rep2 class1 40 60
rep2 class3 10 30
rep2 class3 80 10
rep3 class3 30 20

and get from it

Sales Rep inventoryClass JUL CUR_MTD_REV
rep1 class1 90 110
rep1 class2 50 40
rep2 class1 40 60
rep2 class3 90 40
rep3 class3 30 20

or

Sales Rep inventoryClass total
rep1 class1 200
rep1 class2 90
rep2 class1 100
rep2 class3 130
rep3 class3 50

or

Sales Rep inventoryClass total
rep1 class1 80
rep1 class1 40
rep1 class1 80
rep1 class2 90
rep2 class1 100
rep2 class3 40
rep2 class3 90
rep3 class3 50

 

[ryguy7272]

Ah!! I think we are close to a solution now! Based on what you posted, it
would be option #2. Basically, I wanted to sum two items from two tables.
So, I want to sum 'CUR_MTD_REV' from tblRegion, and 'JUL' from tblRegion.
Also, I want to sum 'Current_Revenue' from tblRM_MediaSales2008 and 'JUL'
from tblRM_MediaSales2008. Finally, I need to sum only 'Class 2' items.

I just can't understand why things get totally screwy when I add Fields from
the tblRM_MediaSales2008. When I just use items from tblRegion, everything is
lovely. See the SQL below:
SELECT tblRVP_Mapping.RVP, tblRVP_Mapping.Director, tblRVP_Mapping.[Sales
Rep], tblRegion.inventoryClass, ([tblRegion]![CUR_MTD_REV]+[tblRegion]![JUL])
AS Total
FROM (tblRM_MediaSales2008 RIGHT JOIN tblRVP_Mapping ON
tblRM_MediaSales2008.SalesRep = tblRVP_Mapping.[Sales Rep]) LEFT JOIN
tblRegion ON tblRVP_Mapping.[Sales Rep] = tblRegion.SalesRep
GROUP BY tblRVP_Mapping.RVP, tblRVP_Mapping.Director, tblRVP_Mapping.[Sales
Rep], tblRegion.inventoryClass, ([tblRegion]![CUR_MTD_REV]+[tblRegion]![JUL])
HAVING (((tblRegion.inventoryClass)="Class2"));

So this is fine, but when I add in two more fields from the
tblRM_MediaSales2008, everything is NOT lovely.

Again, I need scenario #2, with all summing in one single column. Is it
difficult to calculate the other two outcomes? I would be curious to see the
SQL for scenario #1 and Scenario #3 as well. I think this is going to be a
great learning opportunity for me.

Thanks so much,
Ryan---

 

[ryguy7272]

I think I am finally beginning to understand this issue. When I query by
‘RVP’, ‘Director’, ‘Sales Rep’, ‘CUR_MTD_REV’, ‘JUL’, and filter for ‘Class
2’, I get my desired results. SQL below:

SELECT tblRVP_Mapping.RVP, tblRVP_Mapping.Director, tblRVP_Mapping.[Sales
Rep], tblRegion.CUR_MTD_REV, tblRegion.JUL, tblRegion.inventoryClass
FROM (tblRM_MediaSales2008 INNER JOIN tblRVP_Mapping ON
tblRM_MediaSales2008.SalesRep = tblRVP_Mapping.[Sales Rep]) INNER JOIN
tblRegion ON tblRVP_Mapping.[Sales Rep] = tblRegion.SalesRep
GROUP BY tblRVP_Mapping.RVP, tblRVP_Mapping.Director, tblRVP_Mapping.[Sales
Rep], tblRegion.CUR_MTD_REV, tblRegion.JUL, tblRegion.inventoryClass
HAVING (((tblRegion.inventoryClass)="Class2"));

When I try to sum a few elements from another table, named
‘tblRM_MediaSales2008’, related to another table, named ‘tblRegion’, by a
third table, named ‘tblRVP_Mapping’, I get some totally unexpected results.
I think this has to do with the filter for ‘Class 2’, which is a field in the
‘tblRegion’, but not in the ‘tblRVP_Mapping’. I think when I begin to add in
fields from ‘tblRVP_Mapping’, Access starts to create a Cartesian Product (I
just learned about this concept), but I think there really is a defined join
between tables in a query – the ‘tblRVP_Mapping’. Anyway, this is the SQL
when I add in a field named ‘Current Revenue’ from ‘tblRM_MediaSales2008’:

SELECT tblRVP_Mapping.RVP, tblRVP_Mapping.Director, tblRVP_Mapping.[Sales
Rep], tblWandaRegion.CUR_MTD_REV, tblWandaRegion.JUL,
tblRM_MediaSales2008.Current_Revenue, tblWandaRegion.inventoryClass
FROM (tblRM_MediaSales2008 INNER JOIN tblRVP_Mapping ON
tblRM_MediaSales2008.SalesRep = tblRVP_Mapping.[Sales Rep]) INNER JOIN
tblWandaRegion ON tblRVP_Mapping.[Sales Rep] = tblWandaRegion.SalesRep
GROUP BY tblRVP_Mapping.RVP, tblRVP_Mapping.Director, tblRVP_Mapping.[Sales
Rep], tblWandaRegion.CUR_MTD_REV, tblWandaRegion.JUL,
tblRM_MediaSales2008.Current_Revenue, tblWandaRegion.inventoryClass
HAVING (((tblWandaRegion.inventoryClass)="Class2"));

When I run the query, I get all kinds of screwy results!! I would like to
query for RVP, Director, Sales Rep, filter for Class 2, and sum the following
‘CUR_MTV_REV’ + ‘JUL’, (both from ‘tblRegion’) + ‘Current_Revenue’ + ‘JUL’
(both from ‘tblRM_MediaSales2008’). How can I achieve this kind of query?
I’m sure it’s possible, I just don’t know how to do it. Any help would be
greatly appreciated.

Kind Regards,
Ryan---

--
RyGuy

Ah!! I think we are close to a solution now! Based on what you posted, it
would be option #2. Basically, I wanted to sum two items from two tables.
So, I want to sum 'CUR_MTD_REV' from tblRegion, and 'JUL' from tblRegion.
Also, I want to sum 'Current_Revenue' from tblRM_MediaSales2008 and 'JUL'
from tblRM_MediaSales2008. Finally, I need to sum only 'Class 2' items.

I just can't understand why things get totally screwy when I add Fields from
the tblRM_MediaSales2008. When I just use items from tblRegion, everything is
lovely. See the SQL below:
SELECT tblRVP_Mapping.RVP, tblRVP_Mapping.Director, tblRVP_Mapping.[Sales
Rep], tblRegion.inventoryClass, ([tblRegion]![CUR_MTD_REV]+[tblRegion]![JUL])
AS Total
FROM (tblRM_MediaSales2008 RIGHT JOIN tblRVP_Mapping ON
tblRM_MediaSales2008.SalesRep = tblRVP_Mapping.[Sales Rep]) LEFT JOIN
tblRegion ON tblRVP_Mapping.[Sales Rep] = tblRegion.SalesRep
GROUP BY tblRVP_Mapping.RVP, tblRVP_Mapping.Director, tblRVP_Mapping.[Sales
Rep], tblRegion.inventoryClass, ([tblRegion]![CUR_MTD_REV]+[tblRegion]![JUL])
HAVING (((tblRegion.inventoryClass)="Class2"));

So this is fine, but when I add in two more fields from the
tblRM_MediaSales2008, everything is NOT lovely.

Again, I need scenario #2, with all summing in one single column. Is it
difficult to calculate the other two outcomes? I would be curious to see the
SQL for scenario #1 and Scenario #3 as well. I think this is going to be a
great learning opportunity for me.

Thanks so much,
Ryan---

--
RyGuy

are you trying to get something like

Sales Rep inventoryClass JUL CUR_MTD_REV
rep1 class1 30 50
rep1 class1 10 30
rep1 class1 50 30
rep1 class2 50 40
rep2 class1 40 60
rep2 class3 10 30
rep2 class3 80 10
rep3 class3 30 20

and get from it

Sales Rep inventoryClass JUL CUR_MTD_REV
rep1 class1 90 110
rep1 class2 50 40
rep2 class1 40 60
rep2 class3 90 40
rep3 class3 30 20

or

Sales Rep inventoryClass total
rep1 class1 200
rep1 class2 90
rep2 class1 100
rep2 class3 130
rep3 class3 50

or

Sales Rep inventoryClass total
rep1 class1 80
rep1 class1 40
rep1 class1 80
rep1 class2 90
rep2 class1 100
rep2 class3 40
rep2 class3 90
rep3 class3 50

 

[Lord Kelvan]

the problem is you are suffering from that catastrophic cascade
problem when it joins up everything to everything but if you have
joins it shouldnt but it is because you are using the same identifier
in all your tables as the joining mechnisim i just ran a simple test
and where i should have got 12 i got 30

my data was

table1
val
a

table2
val num
a 1
a 2
a 3

table3
val num1 num2
a 1 1
a 2 2

if you sum and group the value as we see it should be 12

1 + 2 + 3 + 1 + 1 + 2 + 2

but what the database is doing is

1 + 1 + 1 +
1 + 2 + 2 +
2 + 1 + 1 +
2 + 2 + 2 +
3 + 1 + 1 +
3 + 2 + 2 +

which = 30

i achieved this by using any combination of joins between the three
tables when attempting the sum the values up meaning it is not
possible to directally do this

my solution would be to do a multitude of queries

query1
select table1.val,sum(table2.num) as total
from table1,table2
where table1.val=table2.val
group by val;

query2
select table1.val, sum(table2.num1+table2.num2) as total
from table1,table3
where table1.val=table2.val
group by val;

query3

select query1.val, query1.total+query2.total as grandtotal
from query1,query2;

where table1 is mapping
table2 is mediasales
and table3 is region

query1 would sum up all the values for the mediasales based on the
values in mapping ie the sales rep
query2 would sum up all the values for the region based on the values
in mapping ie the sales rep
query3 would add the values together into one table


the problem is you wont be able to filter it as you said the invintory
class is not in the media sales but this is the only way to aviod the
cascade problem

ill let you try to build your queries but it shouldnt be too hard if
you cannot build the queries how i have noted as a base framework then
tell me and ill do it tomorrow as it is home time for me

hope this helps for you

Regards
Kelvan

 

[Lord Kelvan]

as a quick note you wont be able to select invintory class the only
values you can select from the region and the mediasales is the vales
summed up as a total value if you select any other values from those
tables it will give you false information as it belongs to one table
and not the other and in the final query we are combining the values
so they must be able to be seen together in some form so i am tryign
to say you cannot select invintoryclass at all in your query

 

[ryguy7272]

Thanks so much for getting me on track LK!! I ended up getting the results
that I needed with a series of three queries. I guess I just though I could
do everything in one single query, but finally realized what you so
eloquently stated above...some of the information belongs to one table and
not the other and the Inventory Class requirement was just totally throwing
things off. So, anyway, it is working now and I learned something new too.
I really don't know that much about queries so I don't know all the limits or
possibilities.


Thanks again!!
Ryan---

 

[Lord Kelvan]

well as a note you can do some powerful things in microsoft queries as
it dosnt have a procedure language so you can do things liek iif
statements in the query as well for conditions and as a note that is
iif not if but it follows the same format as the excel if statement

i was glad to help

regards
kelvan

 

[Lord Kelvan]

as a note i had a revelation last night in bed (dont ask i am a crazy
database lover) on why it was happening and i thoguh i would tell you
what was happening is that the query did one half first table1 joining
to table2 and got the results thoes results had in my example 2 resuts

it the took that result and connected it to table 3 so you have two
sets of 3 results and this was all due to that you used the same
identifier to identify the records between the three tables which is
why you got the mucked up results you should be aware that using the
same key all over the place can create problems such as this

regards
kelvan

 

[ryguy7272]

Thanks for all the information!!

Regards,
Ryan---