sales per month

[benc]

i would like to create a query that shows how many cars a salesman has sold each month.
The code i have for count the number of sales by each staff is:

SELECT CustomerSale.[SalesID#SoldBy], Count(CustomerSale.[SalesID#SoldBy]) AS [CountOfSalesID#SoldBy]
FROM CustomerSale
GROUP BY CustomerSale.[SalesID#SoldBy];

I have a field: CustomerSale.DateOfSale

how can i encorporate this to devide the count within the twelve months of a year.

Also does anyone know how to export this data from a query straight into a graph, preferably in a report in access.

 

[Geoff Codd]

Add another field to your query and enter

Month Sold: Format([DateOfSale],"mmmm 'yy")

this will convert the date of sale to the month of sale with the year ie April '02

Now you can group by this field as well, so your query should now show no. of cars sold by salesperson and month.

For the graph just use the chart wixard under reports and use the querty as your data source

[This message has been edited by Geoff Codd (edited 03-18-2002).]

 

[amyrobb]

Sales Per Month and Sales Year-To-Date

I have an additional question as a follow-up to the original post. If you have a table/query that lists the salesperson's sales revenue per month, how can you write a query that will show that salesperson's sales revenue year-to-date when a specific month is selected?

For example, if I have a table that lists salespeople, their sales revenue for the month, and the number of the month the sales were made:

[Salesperson] [Revenue] [Month_Number]
Robert 100,000 1
Robert 250,000 2
Robert 150,000 7
Tracy 300,000 2
Tracy 325,000 3
Tracy 275,000 4
Gary 175,000 1
Gary 200,000 2
Gary 250,000 3


I have another table that sets [Month_Number] equal to a [Month]:

[Month_Number] [Month]
1 January
2 February
3 March
4 April

A form allows a user to select the month from a dropdown list.

How can I write a query that will display the year-to-date sales revenue for each salesperson based on the month that is selected from the dropdown list?