Remove certain group record from report

[467264]

Hi all,

In MS ACCESS report, how can I remove data in report which has sub-
total is less than certain amount?
Certain amount is not a parameter value but a hard-coded value.
For instance...
I have a sales report for each state which has two column - state and
amount.
I make a group by state first and add a sub-total of amount for each
group.
Now, I want to drop records for state which has sub-total is less than
$1000.
In addition, I want to do it without using query.
In other words, it will be the best if I can do it using expression or
VBA.
Since I am not familiar with Access, I have no idea what will be the
best practice to do it.

Thank you

 

[Allen Browne]

You must do this in the source query for the report. The simplest solution
will be to make a totals query, and then join it to your existing query.

1. Create a new query using the table(s) that give you the state and amount.

2. Depress the Totals button (toolbar.)
Access adds a Total row the the query design grid.

3. Drag the State field into the grid.
In the Total row under this field, accept Group By.

4. Drag the amount field into the grid.
In the Total row under this field, choose Sum.
In the Criteria row under this field, enter:

1000

5. If you need criteria under any other fields, add those fields and choose
Where in the Total row under these, and type the criteria into the Criteria
rows.

6. Test: it should output only the fields that totalled at least 1000. Save
the query as (say) qryState1000up. Close.

7. Open the query you already have, and add the qryState1000up as an input
'table.'

8. Drag the State field from qryState1000up, and drop it onto the State
field in your main table. Access shows a line joining the 2 tables in the
upper pane of query design.

This join limits your query to states with a total of more than 1000.
Therefore that's what you get in the report based on this query.