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!!!

 

[lespaul]

maybe some sample data would help? what do you want to subtract when Table2 has categories that Table1 doesn't? should it be a negative amount?

Leslie

Have you met Hardy Heron?

 

[aarellano]

here is an example

Table1
Month	Category	Class	Dollars
1	custom	A	5
1	standard	A	8
1	custom	B	9
1	standard	B	3
1	custom	C	2

Table2
Month	Category	Class	Dollars
2	custom	A	5
2	standard	A	8
2	custom	C	2
2	standard	C	1
2	custom	D	8
2	standard	D	3
2	custom	E	6
2	standard	E	4
2	custom	F	0
2	standard	F	1

Table3
Category	Class	Dollars
custom	A	8
standard	A	8
custom	B	3
standard	B	6
custom	C	4
standard	C	5
custom	D	8
standard	D	9

custom	E	8
standard	F	8
custom	F	3
standard	G	6
custom	G	4
standard	H	5
custom	H	8
standard	I	9
custom	I	1

So I would like to do the following

(Table1.Dollars - Table2.Dollars) + Table3 Dollars
Total
Category	Class	Dollars
custom	A	8
standard	A	8
custom	B	12
standard	B	9
custom	C	4
standard	C	4
custom	D	0
standard	D	6
		-6
custom	E	4
standard	F	8
custom	F	2
standard	G	6
custom	G	4
standard	H	5
custom	H	8
standard	I	9
custom	I	1

 

[lespaul]

ok, if you use the [ignore][tt][/ignore] tags then the information lines up better.

I still can't see the logic you want to use for the subtraction....Here's the first record in Table1:
[tt]
Month Category Class Dollars
1 custom A 5[/tt]

how does that tie to anything in Table2? There is no record in Table2 that has a Month = 1?

What is the -6 in the middle of your result set? What does that mean?

Exactly which records from Table2 do you want to subtract from which records in Table1?




Leslie

Have you met Hardy Heron?

 

[aarellano]

it would be linked by class and category, those are the only two common links

[tt]
   table1                  table2           - table1 +table2
month code class $  month code class $       code   class  $
1     cust   A   1    2   cust   A   5       cust    A     3
1     std    A   6    2   std    A   2       std     A    -4
                      2   cust   B   4       cust    B     4
                      2   std    B   5       std     B     5
1     cust   C   3    2   cust   C   5       cust    C     2
1     std    C   9                           std     C    -9
[/tt]

table3          -table1 +table2      FinalTotal
code class $    code   class  $   code  class  $
cust   A   7    cust     A    3   cust    A    10
std    A   9    std      A   -4   std     A    5
cust   B   1    cust     B    4   cust    B    5
std    B   7    std      B    5   std     B    12
cust   C   3    cust     C    2   cust    C    5
std    C   6    std      C   -9   std     C   -3 
cust   D   4                      cust    D    4
[/tt]

 

[lespaul]

[tt]

table1 table2 - table1 +table2
month code class $ month code class $ code class $
1 cust A 1 2 cust A 5 cust A 3
1 std A 6 2 std A 2 std A -4
2 cust B 4 cust B 4
2 std B 5 std B 5
1 cust C 3 2 cust C 5 cust C 2
1 std C 9 std C -9[/tt]

they must do math differently where you're from....
table1.code = cust
table1.class = A
table1.$ = 1
table2.code = cust
table2.class = A
table2.$ = 5
5 - 1 = 4

additionally you said in both your previous posts that it was Table1.dollars - Table2.dollars, but in this example you have 2.$ - 1.$?

Hard to help when there's no consistency.....Will all the records be in Table3 or will there be more missing/extra records in that table?
maybe something like this will work:

SELECT T.CODE, T.CLASS, T.DOLLAR + SUM(A.DOLLAR) FROM
(SELECT CODE, CLASS, (DOLLAR * -1) As Dollar FROM TABLE1
UNION
SELECT CODE, CLASS, DOLLAR FROM TABLE2) AS A
INNER JOIN Table3 T ON A.CODE = T.CODE AND A.CLASS = T.CLASS 
GROUP BY CODE, CLASS

Leslie

Have you met Hardy Heron?

 

[aarellano]

Leslie

I was doing something like this

SELECT table1.Category, table1.Class, (-[table2].[SumOfDollars] + [table1].[SumOfDollars]) AS finalddd
FROM table1 INNER JOIN table2 ON (table1.Class = table2.Class) AND (table1.C = table2.Category);

This gives me alsmost what I am looking for in the
-table2 + table1

the only thing is that I have a negative quantity in table2 that is not showing up in table1. I think is because there is no dollars for that category in table1

 

[aarellano]

P.S.

Sorry about the inconcistency of the tables, but that was changed on me as well.

 

[lespaul]

Did you try my solution? BY using a UNION query in the FROM clause, you'll get all the information from both tables....A INNER JOIN between tables 1 and 2 won't work because you will only get records where the data matches you won't get the ones that are in one but not two and the ones that are in two but not one.

Leslie

Have you met Hardy Heron?

 

[aarellano]

Leslie,

Yes I tried to use your code but I am a bit confused, where does the a.code and a.class come from? shouldn't it have the name of the table instead of the a.
table1.code = table2.code?

 

[lespaul]

SELECT T.CODE, T.CLASS, T.DOLLAR + SUM(A.DOLLAR) FROM
[b](SELECT CODE, CLASS, (DOLLAR * -1) As Dollar FROM TABLE1
UNION
SELECT CODE, CLASS, DOLLAR FROM TABLE2)[/b] AS A
INNER JOIN Table3 T ON A.CODE = T.CODE AND A.CLASS = T.CLASS
GROUP BY CODE, CLASS

I'm using a query instead of a table for the source. If you run the query bolded above you get a record set that contains all the records from your Table1 and Table2, I've even multipled the dollar amount in table1 by (-1) so that when you do the SUM in the outside query it subtracts! You have to assign that query an alias (A) in order for Access to run the query.

Leslie

Have you met Hardy Heron?

 

[aarellano]

Ok i tried it again and I must be doing something wrong

so here is what I did

my real query names are
currentqry (from table1)
previousqry(from table2
grssqry(from table3)

I have the sum of dollars for all the categories and classes in each query. I know you might think I am a real pain but as if it werent enough
the category is now changed to continent no big deal I think


so I did this

SELECT currentqry.Category, currentqry.Continent, [currentqry].[SumOfCDDPVA]
(SELECT Category, Continent, ([currentqry].[SumOfCDDPVA] * -1) As Dollars FROM currentqry
union
SELECT proviousqry.Category, proviousqry.Continent, [proviousqry].[SumOfCDDPVA] from proviousqry)
AS A
INNER JOIN grssqry currentqry ON grss.CODE =currentqry.CODE AND grss.CLASS = currentqry.CLASS
GROUP BY CODE, CLASS


[/class]

and I get a "THIS OPERATION IS NOT ALLOWED IN SUBQUERIES"
this is what is highlighted
[code]
SELECT Category, Continent, ([currentqry].[SumOfCDDPVA] * -1) As Dollars FROM currentqry
union
SELECT proviousqry.Category, proviousqry.Continent, [proviousqry].[SumOfCDDPVA] from proviousqry)

cannot figure out why

 

[aarellano]

Ok so I changed the field names and table names and it is still not working
I guess I don't understand where that t.xxx and the a.xxx are coming from

 

[lespaul]

ok your SQL above is:
1. missing the FROM clause
2. has the wrong syntax in the JOIN statement:
[tt]INNER JOIN grssqry currentqry ON grss.CODE =currentqry.CODE AND grss.CLASS = currentqry.CLASS[/tt]

try this:

SELECT A.Category, A.Continent, G.DollarField + Sum(A.Dollars) As FinalTotal
[b]FROM[/b] 
(SELECT Category, Continent, [SumOfCDDPVA] * -1 As Dollars FROM currentqry
union all
SELECT Category, Continent, [SumOfCDDPVA] from proviousqry)
AS A
INNER JOIN grssqry G ON G.CODE =A.CODE AND G.CLASS = A.CLASS
GROUP BY G.CODE, G.CLASS

Leslie

Have you met Hardy Heron?

 

[aarellano]

Leslie, I thank so very, very much for all your help. I was reading on the aliases for the queries. but I think I am about to give up. I have tried different ways to go about this one but nothing seems to be working.
I tried your code and I got and I get the following error
You tried to execute a query that doesnot include the specified expression 'Category' as part of an aggregate function

 

[lespaul]

my bad, the wrong group by, try this:

SELECT A.Category, A.Continent, G.DollarField + 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 G ON G.CODE =A.CODE AND G.CLASS = A.CLASS
GROUP BY A.Category, A.Continent

 

[aarellano]

I am sorry to be a pest but we are almost there,

I get a small box saying "Enter Parameter Value" G.DollarField
I click O.K. and get the following

[tt]
A.Category	Continent	FinalTotal
Custom	Asia	
Custom	Canada	
Custom	USA	
Standard	Asia	
Standard	Canada	
Standard	Europe	
Standard	Latin America	
Standard	USA	

[/tt]

 

[lespaul]

not a problem....what's the SQL that gives the prompt?

 

[aarellano]

SELECT A.Category, A.Continent, G.DollarField+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 (A.Category = G.Category) AND (A.Continent = G.Continent)
GROUP BY A.Category, A.Continent;

I believe that G.DollarField+Sum(A.Dollars) is the issue, the G.DollarField is that the dollar amount from the grssqry? because if it is then the field name is SumOfDDDPVA01 I believe

 

[lespaul]

SELECT A.Category, A.Continent, [b]G.DollarField[/b]+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 (A.Category = G.Category) AND (A.Continent = G.Continent)
GROUP BY A.Category, A.Continent;

replace the bold section above with the actual field name from grssqry that has the dollar amount information.

Leslie

Have you met Hardy Heron?