Query for Unpaid

[emilyebba]

Hi,

I have been trying to figure this out. I have not written a query before. I am trying to make a query that finds out which records have not paid in my database.

I have a table of customers who pay. You enter in those amounts. Some don’t owe anything so they send in a report of $0.00 a month (and in this instance that means paid). I have a checkbox to identify that. So my query would be something like.

List of Unpaid Customers = Customer ID AND Payment Period AND $0.00 in Amount Received Column A AND $0.00 in Amount Received Column B AND Checkbox False

Any help is most appreciated.

Thank you.

 

[Alansidman]

Perhaps if you posted a sample of your database, it might be a little easier to understand. I am not understanding why you have two Payment Recieved Fields, the relationship of the fields in your table, etc. Do a compact and repair before uploading it.

 

[emilyebba]

Thanks. Here is a sample.

In this sample:

Jan 1, 2012 - Jan 31, 2012

Customer 3 did not send anything in. This is who I am trying to find.
Customer 4 sent in 0.00 (zero return = technically paid...just zero)

Two payment types: TOT and BID.

How can I query the database and find records who did not send anything in/didnt pay?

Thanks!!

 

[Alansidman]

Misread the request. Recalculating

 

[Alansidman]

Try this Query

SELECT Customers.FirstName, Customers.LastName, Payments.TOTDue, Payments.BIDDue, Payments.ZeroReturn, Payments.CustomerID
FROM Customers LEFT JOIN Payments ON Customers.ID = Payments.CustomerID
WHERE (((Payments.CustomerID) Is Null));

 

[emilyebba]

Hi, this question may sound simple...but I am new to writing querys. In the Query window in design view...how would I add in the above? I add the names from the table list and then the type in SELECT or LEFT Join etc? Can you send a screen shot or explain? Sorry to ask and thank you so much for your response!!!

 

[mahenkj2]

1. Create

2. Query Design

3. Do not select any table and close the table selection window

4. In the top left, you would see SQL, press that button.

5. You would see Select; already written, delete that.

6. Now copy the whole text shown above and paste it.

7. change the query view.

done.

 

[emilyebba]

I forgot to add that I will be running this query monthly and generate a report. Thanks again for answering my question!!! Much appreciated!!!

 

[emilyebba]

i.e. where would I enter in the date parameters?

 

[emilyebba]

Hi I entered in the above and it worked...except it found the record that was the "Zero Return". That is not what I need to do. I have the checkbox for zero return so that I can keep track of those that "paid" zero. I am looking for the records who did not report anything. In the example I sent you all the customers who are in my customer list "Paid" except Customer #3. How would the query be written so that the query would search the customer list and find all the records who did not report at all (i.e. #3)? As I mentioned above I will run this query monthly. Thanks!

 

[mahenkj2]

I appreciate if you could upload a sample dB.

 

[emilyebba]

Hi, Thanks for responding. Here is the sample database. Im trying to find out which in my customers list did not submit a payment. So in effect it would be comparing my payment table to my customer table and finding those customers who did not pay. I hope this makes sense!

 

[mahenkj2]

As far as I know, you first have to have a query date filtered and then you can have a left join to find who has not paid in that particular month.

Just look at attached. I created a form and two queries.

In case it is not what you are looking for, please submit some example data as a result which you want to obtain.

regards.

 

[emilyebba]

Hi.

Thank you so much for helping me. I tried what you suggested and attempted to copy what you have above. I understand the methodology behind it. But I couldnt get it to work. Using my database I was able to get the query working to extract all the data by date (your qryDateFiltered), but not the qryNotPaid. I have attached a sample database with fake data. It is for a pretend pay period of 1/1/2012-2/28/2012.

So here it is:

In the month of January (1/1/2012-1/31/2012) all Certificates (customers) paid.

In February (2/1/2012-2/28/2012) Certificates 5 and 10 did not pay or report anything. That is what I am trying to figure out.

I am hoping to have the query run a report. The report would ask you your date parameters (Start Date and End Data) by month. I have another query running reports and in the criteria section I have Between [Start Date] And [End Date]. It works great. I was hoping to have something similiar when I try to find out who has not paid or reported every month.

Again thank you so much for helping me. I was at a complete road block here and it helps to have someone give you a hand.

Thanks.

 

[emilyebba]

Lastly I saw this in your query:

[FONT=&quot]Between [forms]![PayPeriodFrom]![textfrom] And [forms]![PayPeriodTo]![textto]

What does this mean?


Forgive my Access inexperience if this question is simple![/FONT]

 

[mahenkj2]

In your posted dB, there is no query at all.

 

[emilyebba]

Hi mahenkj2,

Here is my attempt at the query. Please advise! Thank you so much!

 

[mahenkj2]

You want to filter the data month wise as well as you want to find who did not pay in this (a Particular) month. There may be a chance when in this month a particular client did not pay but in last months he paid. So if you just use query with left join and Is Null is not sufficient becuase it will give you who did not pay at all but monthwise summary may not be available.

To do this, I think, first you need to filter the data monthwise and then in that filtered data, use to find who did not pay.

I do not know in what way you are going to filter the data but I though to put a form in which user can input the date as parameter for query. The below is expression for that only.

My query did not include:

Between [forms]![PayPeriodFrom]![textfrom] And [forms]![PayPeriodTo]![textto]

but:

Between [forms]![frmSelectDate]![textfrom] And [forms]![frmselectdate]![textto]

where the forms is forms collection, frmSelectdate is the name for search form and textfrom is the name of field where that date shall be input.

I think after all this you should be able to use trial and error method. In this forum, you should get several such examples. You may read more on parameter queries.

 

[emilyebba]

Im confused. Sorry!

What I understand is to first filter the data showing only the month I am interested in.

Second compare that filtered data with the master customer list and find those customers who are not part of the filtered date query.

Yes?

Im still not sure how to do that. Can you provide instructions? Im new to Access 2003 and very much appreciate you helping me.

 

[mahenkj2]

That is what we doing by using two queries.

we made first query by filtering dates and used this query in another query notpaid.

Finally, we use a custom search form which will call qryNotpaid and when this will run, it will ask for dates inserted as parameter qrydatefiltered.

Have you tried my frmSelectDate form in the dB posted by me earlier.