most profitable month

[Spira]

hi, i would like to know how to make a query to hold information about the profits(money) made by my company every month.

from this i would like to produce a line graph to show the rise and fall of profits over months and years.

i have already created the profit from each transaction
thanks for your help

 

[Spira]

Select Format(YourDate, "yyyy/mm") as ProfitMonth, Sum(Profit) as SumOfProfit
From YourTable
Group by Format(YourDate, "yyyy/mm");

where would i put this?

 

[Spira]

when i input the date (random) it displays the profit total- even when i put in 1888/02

 

[Spira]

I can't tell what you're doing wrong if you don't post your query.

i have attached the database. the query concerned is qryProfit

what i intend to do is record the amount of profit made by the business every month

jan= £1000
Feb= £1100
.etc

from this i would like to make a graph to show the changes in profits over the year. thanks

 

[Spira]

HAVING Format([Date],"mm/yyyy") = [Enter selected mm/yyyy]

whats 'HAVING' ?

 

[Spira]

thanks for your help- one more problem, if there is no profit for that particular month can you get the sum of profit to display 0.00 instead of not displaying anything???

 

[Spira]

You will need a table of months that you want in the output. Then create a query that joins the table of output months with a left join to your present query. That will include a row for the missing months. You'll need to use the Nz() function to replace the null of the "missing" months with 0.

im not very good at queries- how would i do this

 

[Spira]

You will need a table of months that you want in the output. Then create a query that joins the table of output months with a left join to your present query. That will include a row for the missing months. You'll need to use the Nz() function to replace the null of the "missing" months with 0.

ok, ive tried implementing this Nz() function but its still giving me a headache as it doesnt work. here is the SQL and where would i put the function to give me a 0 instaed of nothing

SELECT Format([Date],"mm/yyyy") AS ProfitMonth, Sum(tblJob.Profit) AS SumOfProfit
FROM tblJob
WHERE (((Format([Date],"mm/yyyy"))=[forms]![frmReports]![txtProfitMonth]))
GROUP BY Format([Date],"mm/yyyy");