Create one report using VB code from one single table

[murphy123]

Hello,

I am a begginer user with VBA in Access 2000.
I created an Adjustment audit db that contain batches to be posted. There is only one table involved (tblAdjustment) The batches have several different amounts. I need to create a report that show the percentage amounts audited. The batches are audited by a user on forms that contains the follow amounts:

Amount = or over -($50.00)
Amount Between -($15) and -($49.99)
Amount = -($3.99), -($4.99) and -($10.99)
Amount > 0

I am trying to create one main report that displays the percentage totals for each amount range containing the count of audited ajustments within the amounts above and their percentages.

I know that I can accomplish this result by creating the individual queries and reports for each amount range. But I was hoping someone can direct me into creating some code (if not too complex) to simplify the report output. Or maybe SQL queries?

This report will be run on a daily basis and I would need to enter a date parameter as well.



Here are the some of the fields from my tblAdjustments:

AuditDate AuditorID Batch Amount Audited (checkbox)


The output report that I am looking for:

Ex: TotalAudited TotalAdjustments Perc
"Amount >0" 30 78 38.46

"Amount =<-($50.00)" 60 102 58.82



Can someone help me out?
Thanks.

 

[dhookom]

I would start by creating a table of ranges:
[tt][blue]
tblAdjustRanges
==========================
MinAmt MaxAmt Title
-999999 -50 Less than -50
-49.99 -15 Between -50 and -15
....
[/blue][/tt]

Then you can add this table to a query with some other table and set the criteria under your range determining expression to
[tt][blue]
Between MinAmt and MaxAmt
[/blue][/tt]
You can then group on the tblAdjustRanges table and count or sum other fields.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]