D
Data08
Hi All,
Any help would be greatly appreciated!
I am creating a report that requires total bonusable dollars (real estate
commissions) to display a summary $ figure by month and then underneath this
figure a running sum of the past 12 months on a progressive basis.
E.G.
May 08 $10000
12 Mths to May 08 $150000
I have 3 challenges:
1) The month is not a calendar month i.e. it runs from the 29th of one month
to the 28th of the next. If it was this would not be a problem.
2) The running sum also needs to work on the above month dates and be
progressive. I.e. 12 Months to May 08 needs to add all Bonus$ from 29th
April 07 through to 28th May 08. Then June 08 needs to add all Bonus$ from
29 May 07 through to 28th June 08. That is, the earliest month is dropped
off the formula as you move into the new month.
3) I haven't been able to get the running sum function to sum for the past
12 months on a progressive basis even when I use a normal calendar month.
The report is based on a query which is:
[Repname] - The name of the rep who has listed or sold the property.
[U/C] - This is a Yes/No field - and the query will only pull the records
that have this field as yes.
[U/CDate] - This is the date when commission becomes payable to the rep.
[RepList$] - This is a calculated field that works out the bonusable $'s for
the listing of a property
[RepSold$] - This is a calculated field that works out the bonusable $'s for
the sale of a property
[Bonus$] - This field adds [RepList$] & [RepSold$]
I've separated the List and Sold formula because it makes it easier to read
for me.
The figures are pulled from three tables.
tblRep - contains the rep's name and is linked via a 1 - many relationship
to tblCommissions.
tblCommission - contains the purchase price ([contractprice]) of the
property, the % rate of commission + any deductions required and is the
figure that all formulas are based on.
tblContract - contains the fields [U/C] and [U/Cdate] and are the conditions
of the query.
I have thought of creating a form that requires the user to input all the
month dates and then run a number of subqueries/reports on this, however,
think that there must be a better way.
Any help would be appreciated.
Kind regards
Data08
Any help would be greatly appreciated!
I am creating a report that requires total bonusable dollars (real estate
commissions) to display a summary $ figure by month and then underneath this
figure a running sum of the past 12 months on a progressive basis.
E.G.
May 08 $10000
12 Mths to May 08 $150000
I have 3 challenges:
1) The month is not a calendar month i.e. it runs from the 29th of one month
to the 28th of the next. If it was this would not be a problem.
2) The running sum also needs to work on the above month dates and be
progressive. I.e. 12 Months to May 08 needs to add all Bonus$ from 29th
April 07 through to 28th May 08. Then June 08 needs to add all Bonus$ from
29 May 07 through to 28th June 08. That is, the earliest month is dropped
off the formula as you move into the new month.
3) I haven't been able to get the running sum function to sum for the past
12 months on a progressive basis even when I use a normal calendar month.
The report is based on a query which is:
[Repname] - The name of the rep who has listed or sold the property.
[U/C] - This is a Yes/No field - and the query will only pull the records
that have this field as yes.
[U/CDate] - This is the date when commission becomes payable to the rep.
[RepList$] - This is a calculated field that works out the bonusable $'s for
the listing of a property
[RepSold$] - This is a calculated field that works out the bonusable $'s for
the sale of a property
[Bonus$] - This field adds [RepList$] & [RepSold$]
I've separated the List and Sold formula because it makes it easier to read
for me.
The figures are pulled from three tables.
tblRep - contains the rep's name and is linked via a 1 - many relationship
to tblCommissions.
tblCommission - contains the purchase price ([contractprice]) of the
property, the % rate of commission + any deductions required and is the
figure that all formulas are based on.
tblContract - contains the fields [U/C] and [U/Cdate] and are the conditions
of the query.
I have thought of creating a form that requires the user to input all the
month dates and then run a number of subqueries/reports on this, however,
think that there must be a better way.
Any help would be appreciated.
Kind regards
Data08