Correction

  • Thread starter Thread starter Kostas Tsolis
  • Start date Start date
K

Kostas Tsolis

Sorry for the duplicate post but i feel i haven't
explained correctly previously.
In my db i have table "Purchases" with
fields: "DateOfPurchase", "Product" and "PurchaseAmount".
There is another table "Sales", with
fields "DateOfPurchase", "Product" and "SalesAmount".
How can i create a query with fields:

Year Product PurchaseAmount SalesAmount
2002 124 12000 16000
2002 138 5000 4000
2003 125 2000 0
2004 125 0 600

I have tried using Year([DateOfPurchase]) and setting
criteria for the other date but i face problems with null
values and with years where a product has for example
only purchases or only sales.
Thanks a lot!
 
Hi,



SELECT Year(DateOfPurchase), Product, Nz(Sum(Purchaseamount), 0), Nz( Sum(
SalesAmount), 0)
FROM somewhere
GROUP BY Year(DateOfPurchase), Product


should do. To care about a sum returning a null, just have to change that
null into a 0, like here, using Nz(a, b ) that returns a unless it is null,
then Nz returns b...


Hoping it may help,
Vanderghast, Access MVP
 
Thank you for your time and willingness to help,
the problem still remains: DateofPurchase belongs to a
different table. If I GROUP BY DateofPurchase it won't
show the SalesAmount for the years that have no purchase
but only sales for the specific product.
Thanks again!
-----Original Message-----
Hi,



SELECT Year(DateOfPurchase), Product, Nz(Sum (Purchaseamount), 0), Nz( Sum(
SalesAmount), 0)
FROM somewhere
GROUP BY Year(DateOfPurchase), Product


should do. To care about a sum returning a null, just have to change that
null into a 0, like here, using Nz(a, b ) that returns a unless it is null,
then Nz returns b...


Hoping it may help,
Vanderghast, Access MVP

Sorry for the duplicate post but i feel i haven't
explained correctly previously.
In my db i have table "Purchases" with
fields: "DateOfPurchase", "Product" and "PurchaseAmount".
There is another table "Sales", with
fields "DateOfPurchase", "Product" and "SalesAmount".
How can i create a query with fields:

Year Product PurchaseAmount SalesAmount
2002 124 12000 16000
2002 138 5000 4000
2003 125 2000 0
2004 125 0 600

I have tried using Year([DateOfPurchase]) and setting
criteria for the other date but i face problems with null
values and with years where a product has for example
only purchases or only sales.
Thanks a lot!
Click to expand...


.
Click to expand...
 
Hi,

Not having a record is different of having a record with Null in it. If
you have a table with all the years in it, if not:


SELECT DISTINCT Year( someDates) AllYears FROM somewhere


then, make an outer join from this query, q2, with the query you actually
have, q1:


SELECT q2.AllYears, q1.*
FROM q2 LEFT JOIN q1 ON q2.AllYEars = q1.YearOf



assuming the actual query, q1, has it year( somedate) field aliased as
YearOf.


Note that the years with no data will have NULL as values, not zero. But you
know how to change the Nulls to zeros:


SELECT q2.AllYears, Nz(q1.qty, 0), Nz( q1.somefield, 0 )
FROM q2 LEFT JOIN q1 ON q2.AllYEars = q1.YearOf



Hoping it may help,
Vanderghast, Access MVP



Kostas Tsolis said:
Thank you for your time and willingness to help,
the problem still remains: DateofPurchase belongs to a
different table. If I GROUP BY DateofPurchase it won't
show the SalesAmount for the years that have no purchase
but only sales for the specific product.
Thanks again!
-----Original Message-----
Hi,



SELECT Year(DateOfPurchase), Product, Nz(Sum (Purchaseamount), 0), Nz( Sum(
SalesAmount), 0)
FROM somewhere
GROUP BY Year(DateOfPurchase), Product


should do. To care about a sum returning a null, just have to change that
null into a 0, like here, using Nz(a, b ) that returns a unless it is null,
then Nz returns b...


Hoping it may help,
Vanderghast, Access MVP

Sorry for the duplicate post but i feel i haven't
explained correctly previously.
In my db i have table "Purchases" with
fields: "DateOfPurchase", "Product" and "PurchaseAmount".
There is another table "Sales", with
fields "DateOfPurchase", "Product" and "SalesAmount".
How can i create a query with fields:

Year Product PurchaseAmount SalesAmount
2002 124 12000 16000
2002 138 5000 4000
2003 125 2000 0
2004 125 0 600

I have tried using Year([DateOfPurchase]) and setting
criteria for the other date but i face problems with null
values and with years where a product has for example
only purchases or only sales.
Thanks a lot!
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top