Report Nightmare

[camidon]

I need a report that pulls a total number of taxpayers from a table containing multiple transactions. Here is how it is structured.

Here is what it would look like:

TaxpayerID Date Remitted Amount 1 Remitted Amount2
4837 11\5\1999 45.00 5.00
4958 11\19\1999 56.00 5.00
4958 11\19\1999 45.00 5.00
4958 11\20\1999 48.00 5.00
5963 12\13\1999 56.00 5.00
Okay, this should be pretty good. I have a total of 5 transactions here, but
only 3 taxpayers. What I need returned on my report is (for a given fiscal year)
The total taxpayers (3 in this case) and the sum of columns 3 and 4 (there may be
more remitted amounts, I kept it at 2 for my example). So the report would
return something like:Fiscal year of 10\1\1999 - 9\30\2000Total Taxpayers: 3
Total Remitted Amount: $275.00Any ideas are appreciated.

 

[Rohdem]

I think what you need to use is a totals query. Open a new query and click the totals which is immediately to the right of the show table button. This will give you a line on your query grid called total. Add taxpayerid to the grid and select 'group by' in the total line. Add date to the grid and select 'where' in the total line. Enter the criteria for the date. Add a field to the query that totals the remitted amounts, i.e.

total: [rem1] + [rem2] + [rem3]

In the total line for this field, select 'sum'

Add the taxpayerid field to the grid a second time and in the total line, select 'count'.

Now you should have a sumoftotal field that gives you the total remitted amount and a countoftaxpayerid that gives you the number of taxpayers.

Mike Rohde
rohdem@marshallengines.com

 

[davidd]

create a report using the group values View - Sorting and Grouping
create a group header for Taxpayer ID as well as a group footer.
sort the payments by date.
in the taxpayerID footer add 3 unbound text box and in control source - =Sum([remit1]) =Sum([remit2]) =Sum([remit3]) this will sum all the paynemts for that taxpayerID. hth