Printing For the Month and Total Todate Calcularions

[Lyn Mac]

Hello Everybody and Mabuhay (Longlive)!!!

In Access2K, I would like to print a 3-column report that shows the following:

e.g.
Income Souce/February/Total Todate
1. License / P100.00 / P300.00
2. Fines / 200.00 / 500.00

Income Source column is the listing of sources of income.

February column is total of income for the month of Febraury per income source.

Total Todate column is for the total of income from January to February (or any current month) per income source.


How do I approach this problem?

Any ideas please?

Thank you very much.

Lyn


[This message has been edited by Lyn Mac (edited 02-05-2002).]

 

[AccessPhantom]

One idea:

Steps:

1. Create a crosstab query showing a todate total and monthly total for each income source. Criteria should be the range of months that is needed.
(e.g.) Jan 1, 2002 - Mar 31, 2002. To make the criteria dynamic use a dialog box to input the date range and to be used in the query criteria.

2. In the report, group the data by income source, place the total todate field in this group and the month field.

3. Since the month field has to be dynamic, make an unbound text box and assign its controlsource to the appropriate month.

4. Make the corresponding subtotals, page totals, and grand totals as necessary.

HTH for a start.

 

[Lyn Mac]

After several hours of brain bashing,"sweat" wiping, sleepless nights and the words of wisdom from the AccessPhantom I finally have the report. A million thanx to AccessPhantom.

What I did was to make a select query with the criteria for the Jan 1 of the year and the current month in which the report is to be generated. The criteria is feed from a dialog box.

Then I used a crosstab query which uses the previous select query and displays the record by source of income, month and the totals todate.

The crosstab is the recordsource in the 3-columned report (Source of Income, Month Total, Total Todate). The controlsource of the Month Total is set dynamically to get only the amounts for the current month. My only regret is during the entire manipulation I was not able to avoid the use of global variable, inspite of its disadvantages, from the dialog box until the value was received by the controlsource property of the Month Total in the report.

At any rate, all is well and I'm doing some finishing touches. Surely I will try to change the use of the global variable when I find the technique to do it.

This forum is great.