Same fields Different Months Qry 1

[aarellano]

Hello,

I am trying to write a query, I have 3 tables, 2 with the same field names and the only difference on the 3rd, is that there is no month field.
What I am trying to do is to

(Table1.dollars - Table2.dollars)+Grssdl.dollars

the problem is that Table2 sometimes might have more category thatn Table1 or other times Table1 more than Table2

so I am a bit confused in how to go about writing this query



Table1 Table2 Grssdl
month month class
class class category
category category dollars
dollars dollars


Any help is much appreciated!!!

 

[aarellano]

Leslie,

That was one of the first things that I tried and I got an invalid bracket so I took the brackets out and I got a

"You tried to execute a query that does not include the specified expression 'SumOfDDDPVA01+Sum(A.Dollars)' as part of an aggregate function"
Here is the sql statement that I used

SELECT A.Category, A.Continent, SumOFDDDPVA01 + Sum(A.Dollars) AS FinalTotal
FROM [SELECT Category, Continent, SumOfCDDPVA * -1 As Dollars FROM currentqry
union all
SELECT Category, Continent, SumOfCDDPVA from proviousqry]. AS A INNER JOIN grssqry AS G ON (G.category = A.category) AND (G.continent =A.continent)
GROUP BY A.Category, A.Continent;

 

[lespaul]

ok, then add that expression to the GROUP BY clause:

SELECT A.Category, A.Continent, SumOFDDDPVA01 + Sum(A.Dollars) AS FinalTotal
FROM [SELECT Category, Continent, SumOfCDDPVA * -1 As Dollars FROM currentqry
union all
SELECT Category, Continent, SumOfCDDPVA from proviousqry]. AS A INNER JOIN grssqry AS G ON (G.category = A.category) AND (G.continent =A.continent)
GROUP BY A.Category, A.Continent, SumOFDDDPVA01 + Sum(A.Dollars);

Leslie

Have you met Hardy Heron?

 

[lespaul]

actually use this one!

SELECT A.Category, A.Continent, SumOFDDDPVA01 + Sum(A.Dollars) AS FinalTotal
FROM (SELECT Category, Continent, SumOfCDDPVA * -1 As Dollars FROM currentqry
union all
SELECT Category, Continent, SumOfCDDPVA from proviousqry) AS A INNER JOIN grssqry AS G ON (G.category = A.category) AND (G.continent =A.continent)
GROUP BY A.Category, A.Continent, SumOFDDDPVA01 + Sum(A.Dollars);

 

[aarellano]

Leslie,

Grouping them worked, it gave me totals but the numbers do not match I have to look at that. Thank you for all the wonderful help. I have learned a ton I appreciate it!!

 

[lespaul]

glad to help! Good luck figuring out why the totals aren't right!

Leslie

 

[PHV]

I'd use this grouping:
GROUP BY A.Category, A.Continent, SumOFDDDPVA01

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886