Grouping/Calculating Months

[Mia101]

I have a table which lists rentals, all have start/end dates and a rental fee p.a.

I want to query this table to show rentals that are effective for any part of 2 dates, [Enter Start Date] and [Enter End Date]. The purpose of this query is to calculate rental income between these 2 selected dates.

Now I have this much working, but I want to group all the figures by month, in order to produce a graph to view the rental income by months.... this bit has me stuck....

How can I group all these records, when I only have 2 dates... for eg, start 01-01-06 and end 31-07-06...
I want to see figures for Jan06, Feb06, Mar06...July06etc... rather than just a single total figure for the entire period...

Any ideas?
Help! I'm completely stuck here and have spent hours searching the internet but haven't found anything suitable...

 

[Mia101]

anyone?

 

[RexesOperator]

I use a query to do a search for transactions between two dates (output to a form). Then I use the same query to produce the financial report (output to a report). I enter the start date as the first day of the month and the end date as the last day of the month. Does that produce the information you need for the graphs?

 

[Mia101]

Hi Rexes,
Thanks for replying. The user will be inputting maybe 01-01-06 as the start date and then 31-06-06 as the end date, and it calculates the total rental due for that entire period. But the user wants it to segment the rental into montly sections, for example, to see a total for Jan06, Feb06 etc... for each of the months within the selected period.

Not sure if there's any easy way of doing this or not...

 

[sdawson]

Probably the best way would to use the "Sorting and Grouping" option in the report footer. There are day, week, month etc options for grouping.

Not sure how this would then be used in a graph!

 

[Mia101]

Thanks for your reply. I have tried this and while it's the closest I've gotten it's not enough. It's still just grouping by the months of the 2 parameter dates. I'm looking for something that will also show the months in between.

For example, there is a rental period of 2 years, the user wants to be able to query a 6-month period and view the rent due for the 6 months. The user just wants to enter a start and an end date, so I was hoping access could pick up what months would be between the 2 parameter dates...

I don't think this is something Access seems capable of doing...

 

[Brianwarnock]

I think that you are going to have to do this by code, workout the daily rate, workout howmany days rented in each month and do the calculations.

Thus if the rental period was from 31/Jan to 2/Mar at £365 p a
then Jan £1 FEb £28 Mar £2 except in a Leap year

Brian

 

[stopher]

Thinking off the top of my head here, I'd use a cartesian join to generate a list of periods (be they daily or monthly or whatever). Consider the following to create a daily record list :

- create a simple table consisting of a list of all possible date i.e. a calendar list. the easy way to do this is to create a sequntial list of dates in excel and copy an paste them into the access table.
- create a cartesian join query to generate all dates per rental e.g.

SELECT rentals.rentalno, dates.myDate
FROM rentals, dates
WHERE dates.myDate Between [startdate] And [enddate]

(note the omission of a join statement)

From the results of this query you can generate pretty much anything you want. For example you could decode the dates in months using the month() funtion and then list unique months. Or if you are interesting in charging on a daily basis then run an aggregate query to count dates by month.

If you really are just interested in months then your dates table could just list say the 1st date of each month or maybe the 1st and last date rather than an entire calandar. The important thing is that the cartesian product will generate a set of records that you can make some sense of to the level of detail you need.

The downside of a cartesian join is that it isn't terribly efficient but this may or may not be an issue.

Without knowing how you calculate your rental I can't really offer much more help.

hth
Stopher

 

[sdawson]

Something wrong here or I'm missing something.
I have a couple of reports that summerise data entered daily into a weekly report. I enter a start date and the number of weeks I want to view and the report comes up with a weekly summary for the number of weeks input. I've just changed the "Group on" field in Sorting and Grouping and hey presto! A report grouped monthly showing monthly data.
It also shows the totals for the period in question. viz. a 26 week period will show 6 months data and the total for the 26 weeks.

Is this what you want?

 

[Brianwarnock]

Something wrong here or I'm missing something.
I have a couple of reports that summerise data entered daily

Either you are or we are, I gather that the data is not entered daily but something like Startdate 21/01/06 enddate 24/07/06 rental £250 p.a.

Brian

 

[sdawson]

I am probably!