Yearly Sales Report Summary

[Coco111 via AccessMonster.com]

In northwind 2007 template, yearly sales report summary show total sales for
whole year but in Q1, Q2, Q3 and Q4 then Total. Great appreciated if anyone
can guide me how to do yearly sales report summary but show month by month...
from jan, feb, mar...till dec and total. Thank you.

 

[Arvin Meyer [MVP]]

Use a crosstab query. Open the query grid design view and right-click on a
blank area next to the tables that you want to use. Choose properties. When
the property dialog shows up, add "Jan", "Feb", ... "Dec" to the Column
Headings property.

 

[Coco111 via AccessMonster.com]

Hi Arvin,

Thanks for your step. It work well, thanks. But if no sales in particular
month for each customer, it will be blank..how to display zero "0" in report.
Thank you agian.

Use a crosstab query. Open the query grid design view and right-click on a
blank area next to the tables that you want to use. Choose properties. When
the property dialog shows up, add "Jan", "Feb", ... "Dec" to the Column
Headings property.

In northwind 2007 template, yearly sales report summary show total sales
for

[quoted text clipped - 3 lines]

month...
from jan, feb, mar...till dec and total. Thank you.

 

[John Spencer]

In the column where you are summing the sales change the expression to
CCur(Nz(Sum(Sales),0))
and I believe you will need to change the totals from SUM to Expression.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi Arvin,

Thanks for your step. It work well, thanks. But if no sales in particular
month for each customer, it will be blank..how to display zero "0" in report.
Thank you agian.

Use a crosstab query. Open the query grid design view and right-click on a
blank area next to the tables that you want to use. Choose properties. When
the property dialog shows up, add "Jan", "Feb", ... "Dec" to the Column
Headings property.

In northwind 2007 template, yearly sales report summary show total sales
for

[quoted text clipped - 3 lines]

month...
from jan, feb, mar...till dec and total. Thank you.

 

[Coco111 via AccessMonster.com]

Hi John,

You help me again...I got it...many thanks again : )

In the column where you are summing the sales change the expression to
CCur(Nz(Sum(Sales),0))
and I believe you will need to change the totals from SUM to Expression.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi Arvin,

[quoted text clipped - 11 lines]

 

[Coco111 via AccessMonster.com]

Hi John,

I got one more problem, posted before but still cant solve the problem. May
be you can help... i got many products list but only 200+ of them have stock,
so in query inventory I want it to show only the item that the current level
is not equal to 0..in criteria i put > 0 or Is not Null but it is not work...
message alert me "enter parameter value" ..................

sql for the queries as below:-

SELECT Products.ID AS [Product ID], Products.[Product Name], Products.Size,
Products.[Product Code], Nz([Quantity Purchased],0) AS [Qty Purchased], Nz(
[Quantity Sold],0) AS [Qty Sold], Nz([Quantity On Hold],0) AS [Qty On Hold],
[Qty Purchased]-[Qty Sold] AS [Qty On Hand], [Qty Purchased]-[Qty Sold]-[Qty
On Hold] AS [Qty Available], Nz([Quantity On Order],0) AS [Qty On Order], Nz(
[Quantity On Back Order],0) AS [Qty On Back Order], Products.[Reorder Level],
Products.[Target Level], [Target Level]-[Current Level] AS [Qty Below Target
Level], [Qty Available]+[Qty On Order]-[Qty On Back Order] AS [Current Level],
IIf([Qty Below Target Level]>0,IIf([Qty Below Target Level]<[Minimum ReOrder
Quantity],[Minimum Reorder Quantity],[Qty Below Target Level]),0) AS [Qty To
Reorder], Products.[Standard Cost], Products.Currency, Products.[Exchange
Rate], ([Qty On Hand]*[Standard Cost]*[Exchange Rate]) AS Amount
FROM ((((Products LEFT JOIN [Inventory Sold] ON Products.ID = [Inventory Sold]
[Product ID]) LEFT JOIN [Inventory Purchased] ON Products.ID = [Inventory
Purchased].[Product ID]) LEFT JOIN [Inventory On Hold] ON Products.ID =
[Inventory On Hold].[Product ID]) LEFT JOIN [Inventory On Order] ON Products.
ID = [Inventory On Order].[Product ID]) LEFT JOIN [Products On Back Order] ON
Products.ID = [Products On Back Order].[Product ID]
WHERE ((([Qty Purchased]-[Qty Sold]-[Qty On Hold])>0));

It exactly from northwind 2007 template, I did not change or modify anything.
Can you help me again...thanks...coco

Hi John,

You help me again...I got it...many thanks again : )

In the column where you are summing the sales change the expression to
CCur(Nz(Sum(Sales),0))

[quoted text clipped - 10 lines]

 

[John Spencer]

The problem is that the columns in the where clause DO NOT YET EXIST. They
are created after the where part of the SQL is executed. You need the
calculations that are used in the select clause. Try the following as your
criteria:

Nz([Quantity Purchased],0)-Nz([Quantity Sold],0)-Nz([Quantity On Hold],0)>0

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi John,

I got one more problem, posted before but still cant solve the problem. May
be you can help... i got many products list but only 200+ of them have stock,
so in query inventory I want it to show only the item that the current level
is not equal to 0..in criteria i put > 0 or Is not Null but it is not work...
message alert me "enter parameter value" ..................

sql for the queries as below:-

SELECT Products.ID AS [Product ID], Products.[Product Name], Products.Size,
Products.[Product Code], Nz([Quantity Purchased],0) AS [Qty Purchased], Nz(
[Quantity Sold],0) AS [Qty Sold], Nz([Quantity On Hold],0) AS [Qty On Hold],
[Qty Purchased]-[Qty Sold] AS [Qty On Hand], [Qty Purchased]-[Qty Sold]-[Qty
On Hold] AS [Qty Available], Nz([Quantity On Order],0) AS [Qty On Order], Nz(
[Quantity On Back Order],0) AS [Qty On Back Order], Products.[Reorder Level],
Products.[Target Level], [Target Level]-[Current Level] AS [Qty Below Target
Level], [Qty Available]+[Qty On Order]-[Qty On Back Order] AS [Current Level],
IIf([Qty Below Target Level]>0,IIf([Qty Below Target Level]<[Minimum ReOrder
Quantity],[Minimum Reorder Quantity],[Qty Below Target Level]),0) AS [Qty To
Reorder], Products.[Standard Cost], Products.Currency, Products.[Exchange
Rate], ([Qty On Hand]*[Standard Cost]*[Exchange Rate]) AS Amount
FROM ((((Products LEFT JOIN [Inventory Sold] ON Products.ID = [Inventory Sold]
.[Product ID]) LEFT JOIN [Inventory Purchased] ON Products.ID = [Inventory
Purchased].[Product ID]) LEFT JOIN [Inventory On Hold] ON Products.ID =
[Inventory On Hold].[Product ID]) LEFT JOIN [Inventory On Order] ON Products.
ID = [Inventory On Order].[Product ID]) LEFT JOIN [Products On Back Order] ON
Products.ID = [Products On Back Order].[Product ID]
WHERE ((([Qty Purchased]-[Qty Sold]-[Qty On Hold])>0));

It exactly from northwind 2007 template, I did not change or modify anything.
Can you help me again...thanks...coco

Hi John,

You help me again...I got it...many thanks again : )

In the column where you are summing the sales change the expression to
CCur(Nz(Sum(Sales),0))

[quoted text clipped - 10 lines]

month...
from jan, feb, mar...till dec and total. Thank you.

 

[Coco111 via AccessMonster.com]

Hi John,

You are genius...terrific...my problem is solved again....thanks a million....

The problem is that the columns in the where clause DO NOT YET EXIST. They
are created after the where part of the SQL is executed. You need the
calculations that are used in the select clause. Try the following as your
criteria:

Nz([Quantity Purchased],0)-Nz([Quantity Sold],0)-Nz([Quantity On Hold],0)>0

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi John,

[quoted text clipped - 38 lines]