Profits each month over a year

[burnoutuk]

I need to create a query which will display profits for each month over a year(year entered by user)

I have a table which has the booking date and the total is calculated from other attributes.

I think i need to select all bookings between [start date] and [end date](entered by user) then some how split them into months.

I dont know how to write it though.

From all of this i need to create a report and graph.

 

[pcEars]

The answer is in your question:

between [start date] and [end date]

should be the crieria for your query's date column. Then your query will prompt the user for start date and end date parameters when it is run.

Base your report off that query and use grouping and sorting options to separate the months in the report.

 

[burnoutuk]

Err im not sure what grouping and sorting options are and no idea how to use them.

 

[pcEars]

In report design view, click on View|Sorting and Grouping to open a small window that lets you set those options. Or, if your report hasn't yet been designed, you can use the report wizard to help you out.

 

[burnoutuk]

Right at the moment I open my report and enter a start date and then an end date, my graph is then displayed.

I want it to then display January - December and the profits.

Sorting and grouping opens Field/Expression Sort order
Any ideas what i need to put in?

Thank you for you help so far

 

[pcEars]

try the name of your date field

 

[burnoutuk]

'try the name of your date field'
Not sure what you mean by this.

this is the query the report comes from

SELECT [tblCampsite].[campName], sum([peopleCost]*[people+12]+[peopleCost]*([people-12]/2)+[pitchCost]) AS Profits
FROM tblCampsite, tblBooking, tblPitch
WHERE [tblBooking].[pitchNo]=[tblPitch].[pitchNo] And [tblCampsite].[siteNo]=[tblPitch].[siteNo] And (([tblBooking].[bookingDate])>=[Enter Start Date For Total To Be Calculated] And ([tblBooking].[bookingDate])<=[Enter End Date For Total To Be Calculated])
GROUP BY [tblCampsite].[campName]
ORDER BY [tblCampsite].[campName];

(Included a picture of my graph)

 

[burnoutuk]

Also how i enter the dates for the graph how do i show these dates on the report? so that if the user entered 1/1/2003 and 1/1/2004 these dates would be displayed in the header or something.

 

[Rich]

What's people+- 12, and why are you dividing by2? If you want monthly figures then group by Month

 

[burnoutuk]

Each person has to pay a set amount and under 12's pay half.

The pofits are calculated by pitchcost + people cost.

How can i group by month?

 

[Rich]

Add another field to the query
Date By Month: Format$([YourTable].[YourDateField],"mmmm yyyy")

 

[burnoutuk]

Thank you for the help still cant get it working though..

 

[Rich]

Post an example in 97ver with some dummy data

 

[burnoutuk]

Its got the tables, the profit query i use and two reports (1 profit report and 1 profit report with graph) i need another report with a graph that gives profits for each month.

 

[Rich]

Here you go

 

[burnoutuk]

Thank you so much the query works.


But another problem now my graph... I dont know how to make it so that I have the profits shown, the month the profit is for and some way of knowing which campsite the profit is for.

And is it posible to add some text in the header to say what dates the user has entered?

Thank you for the help Rich

 

[Rich]

Use the graph wizard, base the graph on the Mnthly query

 

[burnoutuk]

Yeah ive tried but get the error 'The Microsoft Jet database engine did not recognize "[Enter start date for total to be calculated]" as a valid field name or expresion.

 

[Rich]

You have to define the Query Parameters as date time.

 

[burnoutuk]

You have to define the Query Parameters as date time.

Not a clue what that means or how to do it

Sorry, all this is really new to me.