ProgEvalJAA
MIS
I'm helping a colleague with a database of three fiscal years' worth of data.
The database has three separate tables as below:
Table 1 - Expenditures
Table 2 - Receipts
Table 3 - Revolving fund transactions.
Within each table, each transaction has a fiscal year field as part of that particular record.
My colleague then used three separate select queries to count the number of records for all three categories as well as sum the amounts for all three categories. In other words, using the GroupBy, Count and Sum functions she used a select query to count the number of expenditure records and arrive at a total amount of expenditures so the result is something like this:
Fiscal Year 2007 - 20 records - $100
Fiscal Year 2008 - 45 records - $250
Fiscal Year 2009 - 30 records - $175
Similar select queries are set up for the receipts and revolving funds.
The three select queries are then linked on fiscal year in another select query, and that query serves as the basis for a report.
The report looks something like this:
2007 - Expenditure Totals - 20 records - $100
2007 - Receipt Totals - 15 records - $150
2007 - Revolving Fund Totals - 2 records - $50
2008 - Expenditure Totals - 45 records - $250
2008 - Receipt Totals - 12 records - $144
2008 - Revolving Fund Totals - 3 records - $75
2009 - Expenditure Totals - 30 records - $175
2009 - Receipt Totals - 15 records - $200
2009 - Revolving Fund Totals - 1 record - $65
When I review the Design of the report, no grouping or sorting appears to have been used to develop it.
Now we want a grand total of expenditures, a grand count of expenditures as well as the same for receipts, and revolving funds. In other words, we want the report to contain the above information, but we want to add something like this:
Overall Totals - Expenditures - 95 records - $525
Overall Totals - Receipts - 42 records - $394
Overall Totals - Revolving Funds - 6 records - $190 records
Does anyone have any thoughts as to the easiest way to proceed with this task? I'm thinking about a subreport, but I'm not sure that would be the preferred method.
The database has three separate tables as below:
Table 1 - Expenditures
Table 2 - Receipts
Table 3 - Revolving fund transactions.
Within each table, each transaction has a fiscal year field as part of that particular record.
My colleague then used three separate select queries to count the number of records for all three categories as well as sum the amounts for all three categories. In other words, using the GroupBy, Count and Sum functions she used a select query to count the number of expenditure records and arrive at a total amount of expenditures so the result is something like this:
Fiscal Year 2007 - 20 records - $100
Fiscal Year 2008 - 45 records - $250
Fiscal Year 2009 - 30 records - $175
Similar select queries are set up for the receipts and revolving funds.
The three select queries are then linked on fiscal year in another select query, and that query serves as the basis for a report.
The report looks something like this:
2007 - Expenditure Totals - 20 records - $100
2007 - Receipt Totals - 15 records - $150
2007 - Revolving Fund Totals - 2 records - $50
2008 - Expenditure Totals - 45 records - $250
2008 - Receipt Totals - 12 records - $144
2008 - Revolving Fund Totals - 3 records - $75
2009 - Expenditure Totals - 30 records - $175
2009 - Receipt Totals - 15 records - $200
2009 - Revolving Fund Totals - 1 record - $65
When I review the Design of the report, no grouping or sorting appears to have been used to develop it.
Now we want a grand total of expenditures, a grand count of expenditures as well as the same for receipts, and revolving funds. In other words, we want the report to contain the above information, but we want to add something like this:
Overall Totals - Expenditures - 95 records - $525
Overall Totals - Receipts - 42 records - $394
Overall Totals - Revolving Funds - 6 records - $190 records
Does anyone have any thoughts as to the easiest way to proceed with this task? I'm thinking about a subreport, but I'm not sure that would be the preferred method.
