Totals in a query (1 Viewer)

[chuck.harrison]

Hello,

I currently have a query that has a column named MESSAGE, and another column named AMOUNT. This MESSAGE column contains text such as:
TRANSACTION DATE NOT WITHIN LP/POE DATE RANGE
TRANSACTION IS NOT ELIGIBLE TO BE APPLIED TO FINANCIAL AID
INVOICE TYPE CANNOT BE APPLIED
and so on.

I am wanting a totals page showing each message, a total count of each message, along with a sum total for each message. What I have so far is not returning anything, and i'm stumped as to what I need to do to get this to work. I'm attaching a screenshot of the query for reference.

Thank you

 

[Gasman]

Use the Count() and Sum() functions and group by your message field

 

[plog]

1. You want 3 fields returned, but only have 2 in your query. If you want each MESSAGE value you need to add that field to your query. Since you want every value the Total line should show Group By.

2. Don't combine calculations and criteria--unless you want to apply the criteria to the result of the calculation. So both the fields you showed should not have criteria on them. Instead ad another MESSAGE field, change the Total line to Where and then add criteria to that.

3. Make sure your criteria is correct. You say you have a MESSAGE value that has "INVOICE TYPE CANNOT BE APPLIED". That does not contain 'WITHIN' and therefore will not end up in the results. That may or may not be what you want, just be careful of your criteria.

 

[chuck.harrison]

Thank you! Both of your replies worked out nicely. Thank you both!