Access Report Sum Filtering Help

[jfirestone]

I have a report created in Access. The fields available and being used are Customer # (A), Document Date (B), Amount 1 (C), Amount 2 (D), Total of 1 & 2 (E).

I have created a report that gives me a subtotal of each Customer as well as a final grand total of all the customers, thereby giving me an idea of which customer owes us money. With this, I end up with 400 customers who do not owe me anything, and 50 that do.

Currently, I get a report showing me every single customer including all of the 0.00 which I have no interest in. Is there a Filter I can put on after I have these subtotals per customer ( I get a subtotal of C, D, & E, but E is the one I am most interested in since it is the one that equals 0.00 most of the time) so that they will not show???

Thank you!

 

[toptech]

Hi There.

If you don't want to see customers that have zero amounts,
you can eliminate them in a query that you use for the report.

In your query where you have amount1(c) and amount2(d), test that both should greater than zero.

This would eliminate all records that have amount1 and amount2 that are zero.

Good Luck.
Toptech

 

[jfirestone]

My problem is, that I need to test the SUM of Amount 1 and 2 over all 4 records for each customer... i.e.

Amount 1 Amount 2 Total
100.00 3.00 103.00
103.00- 0.00 103.00-
250.00 7.00 257.00
200.00- 57.00- 257.00-


This is how the records lay out for each customer... I get a subtotal of all 3 columns, and need to eliminate the ones that have a Zero there. I can't do a query before the report is created, because there is no subtotal in the table.....

Or can I?

 

[JoeMiller]

You can make a subtotal in the query and place your <>0 criteria on the subtotal field. Make a query, drag all the fields you need down into the grid. Make a new column with this expression in the Field:

SubTotal: [Amount 1]+[Amount 2]

Now set a criteria in that column of:

<>0

This will give you all records where the subtotal is not 0 (positive or negative). Set your report to look at this query and you can use your new SubTotal column as the source for a control rather than calculating it in the report!

HTH Joe Miller
joe.miller@flotech.net

 

[jfirestone]

I am probably just not quite understanding this (I apologize - this is one of my first times working with Access and the help and documentation are dismal), but....

I have a subtotal field now, but all I get is the same thing I had before...

Amount 1 Amount 2 Total
100.00 3.00 103.00
103.00- 0.00 103.00-
250.00 7.00 257.00
200.00- 57.00- 257.00-


Maybe I didn't give you as much inof as I should have....

All of those amounts above are for 1 customer. Below the total column, if you added downwards, 103 - 103 + 257 - 257 = 0. This is where I can't seem to get it to work. How Can I get a subtotal for the one customer? When this number of all of 1 customer + and - equals 0, they don't owe me any money, I am not interested in them.

I am trying to use the grouping info in a query, but when I tell it to Group By the Sum of my SubTotal column I made, it just shows the same exact thing as though I had not asked it to make a Sum.

In my report, it shows me Customer #1, their 2 invoices and 2 payments, and a Subtotal of 0 for the customer, but won't let me filter it. how Do I do that in the Query?

 

[JoeMiller]

Remove Amount1 and Amount2 from the query and leave the subtotal, set the subtotal to SUM and all other fields to group by, that should fix it.

The help files in Access are actually quite good, it's reading them and understanding them that is hard. It takes a while to get the hang of how they are describing things, but when it starts to click it all makes sense.

HTH Joe Miller
joe.miller@flotech.net

 

[jfirestone]

When I do this, my query does not change.

I still get the same list, with 4 records that should be getting added together to equal 0, but they are not. I keep getting

Customer SubTotal
5 103.00
5 (103.00)
5 257.00
5 (257.00)


I do not get the total (or subtotal) of the Group (Customer #5) which should be 0....

What am I Missing that isn't giving me the rght info?

 

[JoeMiller]

Ok, now click the Totals button on the toolbar in query editor (looks like Excel Sum Button). Set Total: under Customer to Group By and under SubTotal to Sum. Run the query then you should be all set.
Joe Miller
joe.miller@flotech.net

 

[jfirestone]

GREAT!!! Finally got it!!! Thank you so much!

I think i figured out why it was not working before, and that is because for ease of explanation, I left out the fact that I had several other Columns in my query (Invoice/Payment Date, Invoice/Check Number etc...) Now I realize I did not need (and it actually affected) these in my query.

So here is the next issue (and again I do apologize, but I am very new to this). I Now have a report that I can get the detail of each customer who has a balance <>0. However, the report shows me every invoice and payment. For some customers, this can be into the hundreds of records. Can I further filter (maybe via another query) so that I only get open invoices in my report? Meaning ones that do not have an credits or payments against them?? This way I can limit the report to only show me a new invoice from 7/17/01, rather than everything from 7/17/99 until now?

 

[JoeMiller]

You'll need another query just like this one, to show what invoices have things left on them. Group by Invoice (provided they are unique across all customers, add customer if not unique). And sum on SubTotal, again select only those <>0. Now this query will only show invoices that have a value left on them.

Now in your query that provides all the line item data to the report, add the query that you just made with the invoice grouping. Access should automatically draw a line relating the table your taking data out of to the query you just added. If it does not, make a relationship from InvoiceField to InvoiceField (just like in relationships window). This effectively limits the data in this query to only show matching invoice numbers from the other query (which only has invoices with a balance).

Hopefully this all makes sense to ya! Joe Miller
joe.miller@flotech.net

 

[jfirestone]

Wow! Like you said, I am starting to get it.....

Okay, So now I have my query, displaying only the open invoices (I used me Apply to No field since that is the same for invoices and checks dealing with one invoice- thereby only showing me invoices that have no payments or credits against them).

Now the one point I am stuck on is having the date of the invoice printing on the report. When I add that date to the query, the query shows me everything again. (Maybe because the date for everything is different so it groups by every date)

I tried just adding the date to the report, since the tables and queries are all linked, but it does the same thing that it does in the query.

So I have a great report of Sorted by customer number, displaying name invoice number, total for that invoice, and open invoice total by customer. But I need the date for each invoice on it too.....

Again, Thanx for the help! I would be stuck with nothing without it!

 

[JoeMiller]

Any chance you could send me a copy of the db so I can take a look? I'm a little confused about what's going on at this point...
Joe Miller
joe.miller@flotech.net