If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Remove certain group record from report
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 |
#2
|
|||
|
|||
Remove certain group record from report
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. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "467264" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|