Date Groupings

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to prepare an annual activity report for each of our partner's
capital accounts. The field names are [partner name],[activity date],
[units] and [$ investment]. I have a query that pulls the partner name &
address info from one table and the activity info from another table. I
would like to be able to report 1) Beginning of year balances for units and $
investment, 2) monthly activity totals for units and $ investment and 3) end
of year totals for the items mentioned. I've tried the following expression
in the partner name page footer - iif ([activity date < #01/01/2004#, sum
([units]),0) in order to print the prior year's totals but the output is
showing totals up through today. Any help on reporting items 1 and 2 would
be appreciated.
 
Joel.
1) Beginning of year balances for units and $ investment
= Sum(Abs([Ativity date] < #01/01/2004#)*[Units])
= Sum(Abs([Ativity date] < #01/01/2004#)*[$ Investment])

2) monthly activity totals for units and $ investment
Create a subreport grouped by month and filtered for 2004 only.

3) end of year totals for the items mentioned
Use the same formula's as for 1) but change the 2005 to 2005.

Hope this helps.
Fons
 
Fons,

You're brilliant. Your formula worked perfectly. Thank you. I'm curious,
though, why does one need to use the Abs function in the formula? Joel

Fons Ponsioen said:
Joel.
1) Beginning of year balances for units and $ investment
= Sum(Abs([Ativity date] < #01/01/2004#)*[Units])
= Sum(Abs([Ativity date] < #01/01/2004#)*[$ Investment])

2) monthly activity totals for units and $ investment
Create a subreport grouped by month and filtered for 2004 only.

3) end of year totals for the items mentioned
Use the same formula's as for 1) but change the 2005 to 2005.

Hope this helps.
Fons


Joel said:
I'm trying to prepare an annual activity report for each of our partner's
capital accounts. The field names are [partner name],[activity date],
[units] and [$ investment]. I have a query that pulls the partner name &
address info from one table and the activity info from another table. I
would like to be able to report 1) Beginning of year balances for units and $
investment, 2) monthly activity totals for units and $ investment and 3) end
of year totals for the items mentioned. I've tried the following expression
in the partner name page footer - iif ([activity date < #01/01/2004#, sum
([units]),0) in order to print the prior year's totals but the output is
showing totals up through today. Any help on reporting items 1 and 2 would
be appreciated.
Click to expand...
Click to expand...
 
Not so brilliant, just been monitoring this site for quite some time and have
learned a lot. About the Abs() function. [Ativity date] < #01/01/2004#
checks for a true or false condition, this would return a -1 (minus 1) or 0
(zero), The zero we can work with but the -1 we must convert to 1 in order
to use it in the rest of the formula.
Glad that it worked.
Take care.
Fons

Joel said:
Fons,

You're brilliant. Your formula worked perfectly. Thank you. I'm curious,
though, why does one need to use the Abs function in the formula? Joel

Fons Ponsioen said:
Joel.
1) Beginning of year balances for units and $ investment
= Sum(Abs([Ativity date] < #01/01/2004#)*[Units])
= Sum(Abs([Ativity date] < #01/01/2004#)*[$ Investment])

2) monthly activity totals for units and $ investment
Create a subreport grouped by month and filtered for 2004 only.

3) end of year totals for the items mentioned
Use the same formula's as for 1) but change the 2005 to 2005.

Hope this helps.
Fons


Joel said:
I'm trying to prepare an annual activity report for each of our partner's
capital accounts. The field names are [partner name],[activity date],
[units] and [$ investment]. I have a query that pulls the partner name &
address info from one table and the activity info from another table. I
would like to be able to report 1) Beginning of year balances for units and $
investment, 2) monthly activity totals for units and $ investment and 3) end
of year totals for the items mentioned. I've tried the following expression
in the partner name page footer - iif ([activity date < #01/01/2004#, sum
([units]),0) in order to print the prior year's totals but the output is
showing totals up through today. Any help on reporting items 1 and 2 would
be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top