comparison analysis

  • Thread starter Thread starter Alex
  • Start date Start date
A

Alex

Hi, everybody.
I'm trying to do a production comparison analysis for two
years (2002 and 2003).
We have production table with date, product, and qty
produced.
It's easy to get list of products with qty produced
separately per year. It's easy to get all products from
2003 and products from 2002, which match with 2003.
The problem for me is that some products produced in 2002
were not produced in 2003.
But, to do the comparison we need to have whole list of
products for two years with the qty produced at least for
one of them.
I would appreciate if anybody could advise how to create
this type of query.

Thanks advance.
 
Hi,



Use an outer join. In the designer, right click on the join between the
two lists, and choose the type 2 or the type 3 (keep all records from 2002
and only those matching from 2003). For products in 2002 but not in 2003,
you would get NULL under the selected columns picked from 2003.


SELECT a.*, b.*
FROM ( SELECT * FROM sales WHERE theYear=2002) As a
LEFT JOIN
( SELECT * FROM sale WHERE theYear= 2003) As b
ON a.ItemID = b.ItemID



(with Jet 4.0 )




Hoping it may help,
Vanderghast, Access MVP
 
Alex said:
Hi, everybody.
I'm trying to do a production comparison analysis for two
years (2002 and 2003).
We have production table with date, product, and qty
produced.
It's easy to get list of products with qty produced
separately per year. It's easy to get all products from
2003 and products from 2002, which match with 2003.
The problem for me is that some products produced in 2002
were not produced in 2003.
But, to do the comparison we need to have whole list of
products for two years with the qty produced at least for
one of them.
I would appreciate if anybody could advise how to create
this type of query.
Click to expand...
Hi Alex,

You can do this in one query (plus we could ask user
for what 2 years), but, for now, it may be easiest to see
how to use the queries you have.

assume "tblProduction"
(and that you really are not using
"date," a reserved word, as name of
a field..but only for simplicity. In following
example I will use "proddate")

ID product qty proddate
1 a 3 3/1/2001
2 a 4 4/1/2002
3 a 5 5/1/2002
4 c 6 6/1/2002
5 c 7 7/1/2002
6 b 2 2/1/2003
7 b 3 3/1/2003
8 c 4 4/1/2003
9 c 5 5/1/2003
10 d 6 6/1/2003
11 d 7 7/1/2003
12 a 1 1/1/2004

///////////
qryYr1:

SELECT tblProduction.product,
Sum(tblProduction.qty) AS SumOfqty
FROM tblProduction
WHERE (((tblProduction.proddate)>=#1/1/2002#
And (tblProduction.proddate)<#1/1/2003#))
GROUP BY tblProduction.product;

producing:

product SumOfqty
a 9
c 13

/////////////
qryYr2:

SELECT tblProduction.product,
Sum(tblProduction.qty) AS SumOfqty
FROM tblProduction
WHERE (((tblProduction.proddate)>=#1/1/2003#
And (tblProduction.proddate)<#1/1/2004#))
GROUP BY tblProduction.product;

producing:

product SumOfqty
b 5
c 9
d 13

qryAllProductsBothYears:

SELECT tblProduction.product
FROM tblProduction
WHERE (((tblProduction.proddate)>=#1/1/2002#
And (tblProduction.proddate)<#1/1/2004#))
GROUP BY tblProduction.product;

producing:

product
a
b
c
d

Now all you have to do is LEFT JOIN
"qryAllProductsBothYears" to the Yrx
queries using null-to-zero function on their
SumQty fields.

/////////////
qryCompareY1Y2:

SELECT
a.product,
NZ([y1].[SumOfqty],0) AS Y1SumQty,
NZ([y2].[SumOfqty],0) AS Y2SumQty
FROM (qryAllProductsBothYears AS a
LEFT JOIN qryYr1 AS y1
ON a.product = y1.product)
LEFT JOIN qryYr2 AS y2
ON a.product = y2.product;

producing:

product Y1SumQty Y2SumQty
a 9 0
b 0 5
c 13 9
d 0 13

Please respond back if I have misunderstood.

Good luck,

Gary Walter
 
Thanks a lot, Michel.
-----Original Message-----
Hi,



Use an outer join. In the designer, right click on the join between the
two lists, and choose the type 2 or the type 3 (keep all records from 2002
and only those matching from 2003). For products in 2002 but not in 2003,
you would get NULL under the selected columns picked from 2003.


SELECT a.*, b.*
FROM ( SELECT * FROM sales WHERE theYear=2002) As a
LEFT JOIN
( SELECT * FROM sale WHERE theYear= 2003) As b
ON a.ItemID = b.ItemID



(with Jet 4.0 )




Hoping it may help,
Vanderghast, Access MVP





.
Click to expand...
 
Thanks a lot, Gary.
Everything is working perfectly.
-----Original Message-----

Alex said:
Hi, everybody.
I'm trying to do a production comparison analysis for two
years (2002 and 2003).
We have production table with date, product, and qty
produced.
It's easy to get list of products with qty produced
separately per year. It's easy to get all products from
2003 and products from 2002, which match with 2003.
The problem for me is that some products produced in 2002
were not produced in 2003.
But, to do the comparison we need to have whole list of
products for two years with the qty produced at least for
one of them.
I would appreciate if anybody could advise how to create
this type of query.
Click to expand...
Hi Alex,

You can do this in one query (plus we could ask user
for what 2 years), but, for now, it may be easiest to see
how to use the queries you have.

assume "tblProduction"
(and that you really are not using
"date," a reserved word, as name of
a field..but only for simplicity. In following
example I will use "proddate")

ID product qty proddate
1 a 3 3/1/2001
2 a 4 4/1/2002
3 a 5 5/1/2002
4 c 6 6/1/2002
5 c 7 7/1/2002
6 b 2 2/1/2003
7 b 3 3/1/2003
8 c 4 4/1/2003
9 c 5 5/1/2003
10 d 6 6/1/2003
11 d 7 7/1/2003
12 a 1 1/1/2004

///////////
qryYr1:

SELECT tblProduction.product,
Sum(tblProduction.qty) AS SumOfqty
FROM tblProduction
WHERE (((tblProduction.proddate)>=#1/1/2002#
And (tblProduction.proddate)<#1/1/2003#))
GROUP BY tblProduction.product;

producing:

product SumOfqty
a 9
c 13

/////////////
qryYr2:

SELECT tblProduction.product,
Sum(tblProduction.qty) AS SumOfqty
FROM tblProduction
WHERE (((tblProduction.proddate)>=#1/1/2003#
And (tblProduction.proddate)<#1/1/2004#))
GROUP BY tblProduction.product;

producing:

product SumOfqty
b 5
c 9
d 13

qryAllProductsBothYears:

SELECT tblProduction.product
FROM tblProduction
WHERE (((tblProduction.proddate)>=#1/1/2002#
And (tblProduction.proddate)<#1/1/2004#))
GROUP BY tblProduction.product;

producing:

product
a
b
c
d

Now all you have to do is LEFT JOIN
"qryAllProductsBothYears" to the Yrx
queries using null-to-zero function on their
SumQty fields.

/////////////
qryCompareY1Y2:

SELECT
a.product,
NZ([y1].[SumOfqty],0) AS Y1SumQty,
NZ([y2].[SumOfqty],0) AS Y2SumQty
FROM (qryAllProductsBothYears AS a
LEFT JOIN qryYr1 AS y1
ON a.product = y1.product)
LEFT JOIN qryYr2 AS y2
ON a.product = y2.product;

producing:

product Y1SumQty Y2SumQty
a 9 0
b 0 5
c 13 9
d 0 13

Please respond back if I have misunderstood.

Good luck,

Gary Walter







.
Click to expand...
 
Back
Top