need help finding missing dates from group of records!!!

[sna1maa14]

What I want to do is find out when a customer hasn't paid his bill for the month and say what month/months are unpaid. So say I have a customer with billing info for 3 years. I have customer Id, Name, Bill amount, amount paid and the month/year it was paid on. Now say customer 1 paid every month except for 11/2004 and 1/2005. How do I get the report to put the month or months he didn't pay? I will also want this to continue to do so for years to come or at least as long as I have a job. Thanks for any suggestions.

Sna1maa14

 

[PHV]

Create a table with one field populated with all the months you want info on and then play with the unmatched query wizard.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[eileen1309]

Does each billed amount have an invoice number with a due date? Try creating an aged A/R which will reflect the inv#, Due Date, and the number of days late.

 

[Tigerlili3]

Maybe this is a really simple approach, but if you have the due date and the amount paid fields for each record, couldn't you just query for records where the amount paid is null or 0 (depending on how your application is set up)?

 

[sna1maa14]

Ok let me explain it a little better. I only have records for when they have made a payment. So I would have 12 records for one year. Where as I only have 11. Now what is the best way to find the missing month for that year and say on the report that month so and so and year so and so are unpaid. The table that hold the history only has records for months and year that were paid. Here is the table fields I have.
txtID,txtDisplayname,numMonth,numYear,numAmountDue,numAmountPaid.

 

[sna1maa14]

Never mind. After talking to one of the IT guys it turns out there is another table I can use to get the results I need. Thanks for the help anyways. They are very protective of their info since I don't work for the company yet, Internship. It is like being a temporary Magicians assistant. He will show you how to pull a rabbit out of a hat but not how to make a tiger disappear.