Help!! Looking for an if statement

[MonsterCR7]

Hi All,
I am looking for a formula to put into my access 2007 query screen to be able to recreate an aging report to show only open invoices.

EXAMPLE:
CUST # CUST NAME DOC TYPE APPLY TO NO TOTAL AMOUNT
1 JOHNNYS INVOICE 48015 $600.00
1 JOHNNYS PAYMENT 48015 -$600.00
31 PAULIES INVOICE 48056 $2750.00
31 PAULIES CREDIT MEMO 48056 -$1750.00
31 PAULIES PAYMENT 48056 $1000.00


SO ON AND SO FORTH....What im struggling to do is insert another column that will check the table for the same apply to no and if there are duplicates to then add the totals from both and if that is equal to or greater than 0 then the new column will insert text OPEN if not CLOSED or PAID

CANNOT base the report off customer number because a customer number can have upwards of over 100 invoices, so the formula would have to be based off the apply to number...

PLEASE HELP!!!!
THANKS

 

[alansidman]

Try this:

SELECT Sheet1.[CUST #], Sheet1.[CUST NAME], Sheet1.[APPLY TO N0], Sum(Sheet1.AMOUNT) AS SumOfAMOUNT, IIf([SumofAmount]>=0,"Open","Paid or Closed") AS Expr1
FROM Sheet1
GROUP BY Sheet1.[CUST #], Sheet1.[CUST NAME], Sheet1.[APPLY TO N0];

Assumed your table name was Sheet1. Change accordingly

 

[MonsterCR7]

Hi Alan,
Thank you for the help! Where exactly would i insert this in Access because i do not have a Sheet1, the name of my query is Aging.

 

[alansidman]

Sheet1 is the name I used for your table where the data you showed in the first posting is stored. Substitute your table name for sheet1.

If you open a query in design view, click in the upper left corner where you can change to datasheet view, but select SQL. Post my SQL statement and change the table name.

If your data shown in the first thread is based upon a query named aging, then substitute Aging for Sheet1 in the new query.

 

[MonsterCR7]

Hi Alan,
Thanks again for the help and it appears to be working however, im getting an error "You tried to execute a query that doesnot include the specificed expression 'Cust No.' as part of an aggregate function."

 

[Joe4]

however, im getting an error "You tried to execute a query that doesnot include the specificed expression 'Cust No.' as part of an aggregate function."

Sounds like you also decided to add the "Cust No" field to your query, yes?
If so, and you aren't Aggregating it, you also need to add it to the Group By clause.
Basically, in an Aggregate Query, every field that you are including in your "Select" clause that is not being aggregated (having some function applied to it), needs to should up in your "Group By" clause as well.

 

[MonsterCR7]

Hi All,
Thanks for the help, but i am still having the same issue where for the individual apply to number it is selling me the apply to number is open for both the invoice and payment when in actuality the total of the payment plus invoice = 0 making it closed. Is there a way to incorporate the apply to number into the formula so that it looks in the table for the same apply to number and if the sum of that is greater than 0 the invoice is still open?

 

[Joe4]

Not quite sure I follow what you are saying.
Based on your data sample in the first post, can post you post exactly what your expected outcome should look like?

 

[MonsterCR7]

From my sample information, i want the new formula to be able to check that if the apply to number (invoice #) matches another apply to number in the entire table then do a sum of the total amounts involved with that apply to number, then based on that result if the amount is greater than $0, the invoice is still considered open, if the amount is 0 than the invoice is closed.

FOR EXAMPLE:
For Johnny's the apply to number are the same and the SUM of the two total amounts is $0 therefore that invoice is PAID OR CLOSED.
If the payment for Johhnys was only $300 instead of $600 there would be a remaining balance of $300, meaning the invoice is still OPEN.

 

[Joe4]

I think that is what Alan's code is supposed to do, but it is trying to do a few different steps at the same time, and some times Access doesn't like that. So let's break it up into two steps (queries), and I will tell you how to do it yourself (instead of giving you the code). That way if your syntax is a little different, it won't matter and it will work OK.

Step 1 - Create a Query to Return the Total Amount for Each Invoice
1. Create a new query from your data table, and name it "Invoice_Totals"
2. Add the following fields to your query: Cust #, Cust Name, Apply to No, Total Amount
3. Click on the Totals Button to create an Aggregate Query (button looks like a Sigma)
4. This will add a Totals Row with the words "Group By" under each field. Under the "Total Amount" field, change the words "Group By" to "Sum".
5. Save your query and view the results.

You will see that you know have a query that shows one line per invoice with the combined Total Amount

Step 2 - Create Your Final Query
1. Create a new query from your previous query
2. Add all the fields from your previous query
3. Add a calculated field that uses the Sum from the previous query to return your determination, i.e.

Status: IIf([SumofTotal Amount]>=0,"Open","Paid")

4. Save your query and view the results.

This should show what you want.