I need a customer sales listing that includes no-sales months.

F

Frank Martin

For the purposes of creating a chart, I need a way to produce a grouped
query of sales per month per customer, but I need the non-sales months shown
also.

All my data is in one query called QryINVOICE.

Can someone point me in the right direction.

Thanks Frank
 
G

Gary Walter

Frank Martin said:
For the purposes of creating a chart, I need a way to produce a grouped
query of sales per month per customer, but I need the non-sales months shown
also.

All my data is in one query called QryINVOICE.
Click to expand...
Hi Frank,

This sounds like a perfect candidate for a crosstab
query where your column headings assure a value
for each month, and wrapping the transform sum
of sales in NZ function shows zeroes for months
where no sales.


Example using NorthWind.mdb
(ignoring Discount in Sales summation)

qryCustomerSales

SELECT
Customers.CustomerID,
First(Customers.CompanyName) AS CompName,
Month([OrderDate]) AS SaleMonth,
Year([OrderDate]) AS SaleYear,
Sum([Quantity]*[UnitPrice]) AS Sales
FROM (Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID)
INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
GROUP BY
Customers.CustomerID,
Month([OrderDate]),
Year([OrderDate]);


Customer ID CompName SaleMonth SaleYear Sales
ALFKI Alfreds Futterkiste 1 1998 $851.00
ALFKI Alfreds Futterkiste 3 1998 $491.20
ALFKI Alfreds Futterkiste 4 1998 $960.00
ALFKI Alfreds Futterkiste 8 1997 $1,086.00
ALFKI Alfreds Futterkiste 10 1997 $1,208.00
..........
WOLZA Wolski Zajazd 2 1998 $587.50
WOLZA Wolski Zajazd 4 1998 $1,277.60
WOLZA Wolski Zajazd 7 1997 $808.00
WOLZA Wolski Zajazd 12 1996 $459.00
WOLZA Wolski Zajazd 12 1997 $399.85

make a copy of this query.
change query copy to crosstab.
fill in crosstab row of grid.

Field: CustomerID
Table: Customers
Total: GroupBy
Crosstab:

Field: CompName: CompanyName
Table: Customers
Total: First
Crosstab: RowHeading

Field: SaleYear: Year([OrderDate])
Table:
Total: GroupBy
Crosstab: RowHeading

Field: SaleMonth: Month([OrderDate])
Table:
Total: GroupBy
Crosstab: ColumnHeading

Field: Sales: Sum([Quantity]*[UnitPrice])
Table:
Total: Expression
Crosstab: Value

TRANSFORM Sum([Quantity]*[UnitPrice]) AS Sales
SELECT First(Customers.CompanyName) AS CompName,
Year([OrderDate]) AS SaleYear
FROM (Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID)
INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
GROUP BY Customers.CustomerID, Year([OrderDate])
PIVOT Month([OrderDate]);


If run query now you will get 12 column headings (1-12)
and blanks where no data.

To make sure you always get 12 col headings, change
SaleMonth column in grid:

Field: SaleMonth: "Mth" & Month([OrderDate])
Table:
Total: GroupBy
Crosstab: ColumnHeading

Then, choose Properties for query,
and in "Column Headings", type

"Mth1","Mth2","Mth3","Mth4","Mth5","Mth6","Mth7","Mth8","Mth9","Mth10","Mth1
1","Mth12"


To show $0.00 where there is no data, change
Sales column in grid:

Field: Sales: CCur(Nz(Sum([Quantity]*[UnitPrice])))
Table:
Total: Expression
Crosstab: Value

The SQL should then look like:

TRANSFORM CCur(Nz(Sum([Quantity]*[UnitPrice]),0)) AS Sales
SELECT First(Customers.CompanyName) AS CompName,
Year([OrderDate]) AS SaleYear
FROM (Customers INNER JOIN Orders ON Customers.CustomerID =
Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
GROUP BY Customers.CustomerID, Year([OrderDate])
PIVOT "Mth" & Month([OrderDate]) In
("Mth1","Mth2","Mth3","Mth4","Mth5","Mth6","Mth7",
"Mth8","Mth9","Mth10","Mth11","Mth12");

resulting in (this will probably wrap each record to 2 lines in post):

CompName SaleYear Mth1 Mth2 Mth3 Mth4 Mth5 Mth6 Mth7
Mth8 Mth9 Mth10 Mth11 Mth12
Alfreds Futterkiste 1997 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00
$1,086.00 $0.00 $1,208.00 $0.00 $0.00
Alfreds Futterkiste 1998 $851.00 $0.00 $491.20 $960.00 $0.00 $0.00
$0.00 $0.00 $0.00 $0.00 $0.00 $0.00
..........
Wolski Zajazd 1996 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00
$0.00 $0.00 $0.00 $459.00
Wolski Zajazd 1997 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $808.00 $0.00
$0.00 $0.00 $0.00 $399.85
Wolski Zajazd 1998 $0.00 $587.50 $0.00 $1,277.60 $0.00 $0.00 $0.00
$0.00 $0.00 $0.00 $0.00 $0.00


Hopefully this example will help you adapt your query.

Please respond back if I was not clear about something.

Good luck,

Gary Walter
 
F

Frank Martin

Thank you. I will study this, which may take some time, and report back.
Regards Frank


Gary Walter said:
Frank Martin said:
For the purposes of creating a chart, I need a way to produce a grouped
query of sales per month per customer, but I need the non-sales months shown
also.

All my data is in one query called QryINVOICE.
Click to expand...
Hi Frank,

This sounds like a perfect candidate for a crosstab
query where your column headings assure a value
for each month, and wrapping the transform sum
of sales in NZ function shows zeroes for months
where no sales.


Example using NorthWind.mdb
(ignoring Discount in Sales summation)

qryCustomerSales

SELECT
Customers.CustomerID,
First(Customers.CompanyName) AS CompName,
Month([OrderDate]) AS SaleMonth,
Year([OrderDate]) AS SaleYear,
Sum([Quantity]*[UnitPrice]) AS Sales
FROM (Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID)
INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
GROUP BY
Customers.CustomerID,
Month([OrderDate]),
Year([OrderDate]);


Customer ID CompName SaleMonth SaleYear Sales
ALFKI Alfreds Futterkiste 1 1998 $851.00
ALFKI Alfreds Futterkiste 3 1998 $491.20
ALFKI Alfreds Futterkiste 4 1998 $960.00
ALFKI Alfreds Futterkiste 8 1997 $1,086.00
ALFKI Alfreds Futterkiste 10 1997 $1,208.00
.........
WOLZA Wolski Zajazd 2 1998 $587.50
WOLZA Wolski Zajazd 4 1998 $1,277.60
WOLZA Wolski Zajazd 7 1997 $808.00
WOLZA Wolski Zajazd 12 1996 $459.00
WOLZA Wolski Zajazd 12 1997 $399.85

make a copy of this query.
change query copy to crosstab.
fill in crosstab row of grid.

Field: CustomerID
Table: Customers
Total: GroupBy
Crosstab:

Field: CompName: CompanyName
Table: Customers
Total: First
Crosstab: RowHeading

Field: SaleYear: Year([OrderDate])
Table:
Total: GroupBy
Crosstab: RowHeading

Field: SaleMonth: Month([OrderDate])
Table:
Total: GroupBy
Crosstab: ColumnHeading

Field: Sales: Sum([Quantity]*[UnitPrice])
Table:
Total: Expression
Crosstab: Value

TRANSFORM Sum([Quantity]*[UnitPrice]) AS Sales
SELECT First(Customers.CompanyName) AS CompName,
Year([OrderDate]) AS SaleYear
FROM (Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID)
INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
GROUP BY Customers.CustomerID, Year([OrderDate])
PIVOT Month([OrderDate]);


If run query now you will get 12 column headings (1-12)
and blanks where no data.

To make sure you always get 12 col headings, change
SaleMonth column in grid:

Field: SaleMonth: "Mth" & Month([OrderDate])
Table:
Total: GroupBy
Crosstab: ColumnHeading

Then, choose Properties for query,
and in "Column Headings", type

"Mth1","Mth2","Mth3","Mth4","Mth5","Mth6","Mth7","Mth8","Mth9","Mth10","Mth1
1","Mth12"


To show $0.00 where there is no data, change
Sales column in grid:

Field: Sales: CCur(Nz(Sum([Quantity]*[UnitPrice])))
Table:
Total: Expression
Crosstab: Value

The SQL should then look like:

TRANSFORM CCur(Nz(Sum([Quantity]*[UnitPrice]),0)) AS Sales
SELECT First(Customers.CompanyName) AS CompName,
Year([OrderDate]) AS SaleYear
FROM (Customers INNER JOIN Orders ON Customers.CustomerID =
Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
GROUP BY Customers.CustomerID, Year([OrderDate])
PIVOT "Mth" & Month([OrderDate]) In
("Mth1","Mth2","Mth3","Mth4","Mth5","Mth6","Mth7",
"Mth8","Mth9","Mth10","Mth11","Mth12");

resulting in (this will probably wrap each record to 2 lines in post):

CompName SaleYear Mth1 Mth2 Mth3 Mth4 Mth5 Mth6 Mth7
Mth8 Mth9 Mth10 Mth11 Mth12
Alfreds Futterkiste 1997 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00
$1,086.00 $0.00 $1,208.00 $0.00 $0.00
Alfreds Futterkiste 1998 $851.00 $0.00 $491.20 $960.00 $0.00 $0.00
$0.00 $0.00 $0.00 $0.00 $0.00 $0.00
.........
Wolski Zajazd 1996 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00
$0.00 $0.00 $0.00 $459.00
Wolski Zajazd 1997 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $808.00 $0.00
$0.00 $0.00 $0.00 $399.85
Wolski Zajazd 1998 $0.00 $587.50 $0.00 $1,277.60 $0.00 $0.00 $0.00
$0.00 $0.00 $0.00 $0.00 $0.00


Hopefully this example will help you adapt your query.

Please respond back if I was not clear about something.

Good luck,

Gary Walter
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

I need to find the months without activity. 5
I need a xtab query to show zero months. 1
How do I include zero-sale months in a Crosstab query? 3
I need help with top values 1
Show one report per customer if multiple sales 0
Customer Penetration 3
Query from unrelated tables help please 4
Combining 3 queries 2

Top