Show Open Balance Customers Only

[jfirestone]

HI all!

I need a little help with a query that isn't returning EXACTLY the info I want.

here is what I am currently getting...

Cust NO. , Name , DocType , DocDate , DocNum , DocApply , Subtotal
001436 , NBK , Invoice , 2/3/2004 , 212034 , 212034 , 90.00
001436 , NBK , Pmt , 2/15/2004 ,004346 , 212034 , -90.00
001436 , NBK , Invoice , 2/8/2004 , 212149 , 212149 , 90.00
037582 , ELL , Invoice , 2/15/2004 , 213625 , 213625 , 180.00
037582 , ELL , PMT , 2/25/2004 , 000205 , 213625 , -180.00

I only want this query to show me the customer that has an open balance, show it should only return...

Cust NO. , Name , DocType ,DocDate , DocNum , DocApply , Subtotal
001436 , NBK , Invoice , 2/8/2004 , 212149 , 212149 , 90.00

as this is the only invoice that is actually open. The first invoice for this customer has a payment for the full amount, and the second customer would not show at all since they have no open balance.

This would also show me the customer if they paid only part of their balance.

I essentially need this query to take the 2 (or more) transactions that reference the same DocApply, run a subtotal on them and only show them to me if they don't cancel each other out.

I am sure this is easy, just can't figure it out! Thank you!

 

[Lister]

From what I can see here you are using the same table for both you Invoice and Pmt =[Payments]. I could be wrong. If so you were going to run into this problem sooner or later. It would be better to have Payments and invoices in different tables and linked by DocApply. That way it would be easy to subtract payments for that Invoice number.

But there is a way around this.
I have written three queries to get around the problem.
First thing I had to do, and I don’t know how much of a problem this will be, is change the Pmt from -$90 to a positive $90. You will see why in a moment.
Next, sort your Invoices out, qryInvoices. I have added a new field [CopySub] that mimics the SubTotal. This is what they owe you, correct?
Second your Payments, qryPmt. This I have set up as a group query, That way payments for the same ”job” number are combined. This should sort out part payments
Third, qrySubTotal. This is the trick. I have linked the two queries by [DocApply] as this looks like you ”job” number.
I have then added a new field and code

Balance: [CopySub]-[qryPmt].[SubTotal]

With the criteria >0 Or Is Null
This will then run a calculation on all “job” numbers that are the same. If the amounts in Invoice and Pmt cancel each other out the balance is 0, if there is no payment the balance is Null. But if there is a part payment, the query will return what is owed.

I have attached a sample database to show it better.

Hope this is some help.

 

[jfirestone]

Wow, great breakdown!

I do have all the info in one table, which I don't seem to have a choice about as the program I am pulling this database from has everything together. Right now, I have no choice but to pull my data through Excel as the date coming out of the file looks like this 20040404 rather than a nice 4/4/2004 that Access can handle. Why the heck does Excel have a YMD converter but Access doesn't?

This looks like it should solve my problem, I will play around a little bit and let you know!

Thank you for the help!

 

[jfirestone]

Okay, here is my follow up. This query you set up works almost too well.

What I mean to say is, that it gets rid of too much information.

When the qrySubTotal is run, it only shows the open balance on a particular invoice. Is it possible for this to Show all the transactions relating to an invoice that is not fully paid?

i.e. Customer has invoice of $90 but pays $30. In my report (thats what I am working towards) I would like to see the invoice and the payment both. Not just the balance owed. This way it makes it easy when I make a call and I have all the info on Invoice and check number right there in front of me.

Thank you again for the help!

 

[Rich]

Use a subReport for payments made and deduct the Sum of them from the original invoice amount

 

[jfirestone]

Maybe I need to rethink this for a minute.... i have a different idea of how to solve the problem, just don't know if it is possible.

I have a query only showing me the open invoices and payments for my customers. The problem is I don't have any other info than the Customer Name, Apply to Number, and the ammount.

When I add the date or any other info in, it gives me evrry record for the customer rather than only the open records. BUt I have figured out why. When I only use the apply to number, the rcords that match cancel each other out if they add up to 0. When i add the date, the records don't match anymore. When I add the Document Type (i.e. Invoice, payment) again they don't match anymore.

Is there anyway to have my query display the date and the type, without actually looking at it? Or anyway to tell the query that all dates equal each other and all types equal each other so it does not see a difference?

Or, do I need the query I am already using, and tell the report to give me the extra info?

 

[Lister]

Rich is quite correct. From this point on you would be best to us Subreports or Subforms to sort your data. As this is what databases are all about, sorting and manipulating data.
Since I have the day off (start my new job on Monday, Yipppeeee ), I have done some more work to the first database. Adding a form with SubForm; note here, that you could add payments directly into the subform; and a report for each record.

I really hope this is some help to you.
Let us know how you get on.

Regards

 

[Rich]

So where does one store the invoice details in this instance?

 

[jfirestone]

So where does one store the invoice details in this instance?

The info I am manipulating is all actually handled through another piece of software called Macola. It works on relational databases just like access.

There are about 100 different database files that the program uses. So there is a compltely different file that associates each invoice number to an order number and all of the cusotmer shipping and billing info, and then another file that contans the order number and all of the specific line items associated with it.

Macola has a built in Aging report so that I can see past due customers and all, but no way to modify it. What I want is to have my aging report show contact names, numbers, emails, comments as to whether the cusomter is a slow payer normally or whether they are rude or maybe how I should handle them.

Using access to manipulate the info, I can take all the info from the 5 or 6 database files they are contained in and put them all together for the collections folks to use

 

[jfirestone]

THATS IT!!!

Thanks Pat!! This works perfectly. You are a genius!

Lister, I appreciate the effort, but I don't need to enter any info through Access, just look at the info I am inputing through Macola.

I moved in my table name, my real field names, played with it a little bit (since I didn't get the info in correctly) and voila! I have the report I need.

Thank you!!!

There is no need to change the table structure. It is correct as it stands and payments should be credits as you have them. This sturcture allows you to do queries that just sum records to find balances. There is no need for each query to have logic to determine whether an item is a credit or a debit to know whether to add or subtract each row.

If it is true that DocApply groups payments with the invoice they apply to, then the whole thing is pretty simple.

query1-find balances:
SELECT t.CustNo, t.DocApply, Sum(t.Subtotal) AS SumOfTotal
FROM YourTable AS t
GROUP BY t.CustNo, t.DocApply
HAVING Sum(t.Subtotal) > 0;

query2- get open invoices:
Select t.CustNO, t.Name, t.DocType, t.DocDate, t.DocNum, t.DocApply, t.Subtotal
From YourTAble as t Inner Join query1 as q on t.CustNo = q.CustNo AND t.DocApply = q.DocApply
Order By t.CustNo, t.DocApply;

Query2 will bring back any invoices with outstanding balances plus any partial payments that have been applied. If you only want invoices, add a where clause to query2 to only return invoice transactions.

 

[Rich]

Thanks, now I understand what you're doing never heard of Macola though, bet it cost mega bucks