display sales by month

[Simon]

I have a qry that dispays the products that i sell and how many i have
sold of each.

What i would like to do is dispaly the same data but would also like
to split it up so it displaty how many of each product i sell each
month

How is the best way to build the query to show sales for each month


Thanks

 

[Guest]

Take a look at

http://www.hal-pc.org/journal/04sales.html

it should help you get going.

 

[Matt]

I have a qry that dispays the products that i sell and how many i have
sold of each.

What i would like to do is dispaly the same data but would also like
to split it up so it displaty how many of each product i sell each
month

How is the best way to build the query to show sales for each month

Thanks

You could also use the a Crosstab Query wizard.

Queries -> Design -> Crosstab Query Wizard

 

[Guest]

The link Daniel sent you refers to crosstab queries in which each month would
be shown as a column heading. If you want to group the results in rows
rather than columns, however, then you simply group by the year and month of
each sale date, e.g.

SELECT
ProductID,
YEAR(Saledate) AS SaleYear,
MONTH(SaleDate) As SaleMonth,
COUNT(*) AS NumberSold
FROM Sales
GROUP BY ProductID, YEAR(Saledate) MONTH(SaleDate);

If you do use a crosstab query then you need the month/year as a single
column heading rather than separately as above, for which you can use the
Format function. Here's an example which returns the number of feet drilled
by rigs in each city over a 6 month period:

TRANSFORM SUM(Feet)
SELECT City
FROM DrillLog
GROUP BY City
PIVOT FORMAT(CompleteDate,"mmm yyyy")
IN ("Oct 2005","Nov 2005","Dec 2005","Jan 2006","Feb 2006","Mar 2006");

The IN clause does two things; firstly it causes the columns to be returned
in the correct chronological order rather than sorted alphabetically;
secondly it means any months with no data will be returned rather than just
those with data.

Ken Sheridan
Stafford, England