How to filter a buyer having 1 or 2 payments only.

[moi]

Dear all,

I have a struggle in filtering a buyer having a 1 or 2 number(s) of payments..

Appreciate any help.

Thank you.

 

[GaP42]

You must provide more information. Do you have a payments table? Can you count payments? What have you attempted? Do you want to identify only those orders that have not been fully paid, or all orders? Only orders raised from a user-specified date?

 

[moi]

You must provide more information. Do you have a payments table? Can you count payments? What have you attempted? Do you want to identify only those orders that have not been fully paid, or all orders? Only orders raised from a user-specified date?

Hi GaP42,

Yes sir, i have payments table.. I can count payments each buyer using their ID's (manualy).. Am trying to use the 'count' from my query but i dont how to put something like criteria for the 1 payment and 2 payments.. The reason why i need to print this is, i wanted to send a reminder to the buyer the status of their payments, whether they still want to continue or it will be forfeited..

 

[ebs17]

SELECT BuyerID, COUNT(*) AS NumberPayments
FROM tblPayments
GROUP BY BuyerID
HAVING COUNT(*) <= 2

 

[GaP42]

Using @ebs17 query the results will include all Buyers, including those who have completed purchases through multiple payments, irrespective of date of purchase. Do you have any concerns with those results? If you do not want to include completed purchases, how do you propose to recognise them? Does it matter if a purchase has not been completed that is over 1 yr old, what if it is 5 years old or more? Do you have an ability to write-off such purchases? If so how are they flagged so these purchases can be excluded?

 

[moi]

SELECT BuyerID, COUNT(*) AS NumberPayments
FROM tblPayments
GROUP BY BuyerID
HAVING COUNT(*) <= 2

Hi ebs17, thank you, i will try this one.. What if, i would add 1 more criteria from "remarks" like "holding fee" and "1st monthly payment".. How do i write..

 

[moi]

Using @ebs17 query the results will include all Buyers, including those who have completed purchases through multiple payments, irrespective of date of purchase. Do you have any concerns with those results? If you do not want to include completed purchases, how do you propose to recognise them? Does it matter if a purchase has not been completed that is over 1 yr old, what if it is 5 years old or more? Do you have an ability to write-off such purchases? If so how are they flagged so these purchases can be excluded?

Hello gap42,

Thank you for mentioning all of these scenario, as a newbie i have a very limited insights on the

 

[moi]

Hello gap42,

Thank you for mentioning all of these scenario, as a newbie i have a very limited insights on the matter..

 

[GaP42]

add 1 more criteria from "remarks" like "holding fee" and "1st monthly payment"

@moi - Are Remarks entered as free text? or from a list of phrases? Is only one remark possible against any one payment?
If free text then it is unlikely that you will achieve your desired result through that means. Simple typos/variations entered by users will be excluded.
You need to develop the insights to how to identify full payments and what to do about long-term non payment, and incorporate these into your db / application design. The principal question remains - how do you recognise that the payments made have not completed a purchase? Or how do you know a purchase has been fully paid? Do you have a flag isPaid on the purchase? Do you have a query which determines the sum of the payments = the total purchase price for a purchase? ==> therefore which are not fully paid as at a date?
Note also - in @ebs17 SQL there is nothing to distinguish for a BuyerID that the Payments relate to a particular Purchase. The client may have made multiple purchases, more than one may not be fully paid, other purchases by the client may have been full paid. Do you need to show the Client the outstanding balance against each purchase?
Or are you working toward producing a monthly statement of account? You require, at least, a balance at the start of the month, the sequential list of payments received and purchases made, and the outstanding balance at the end.

 

[moi]

@moi - Are Remarks entered as free text? or from a list of phrases? Is only one remark possible against any one payment?
If free text then it is unlikely that you will achieve your desired result through that means. Simple typos/variations entered by users will be excluded.
You need to develop the insights to how to identify full payments and what to do about long-term non payment, and incorporate these into your db / application design. The principal question remains - how do you recognise that the payments made have not completed a purchase? Or how do you know a purchase has been fully paid? Do you have a flag isPaid on the purchase? Do you have a query which determines the sum of the payments = the total purchase price for a purchase? ==> therefore which are not fully paid as at a date?
Note also - in @ebs17 SQL there is nothing to distinguish for a BuyerID that the Payments relate to a particular Purchase. The client may have made multiple purchases, more than one may not be fully paid, other purchases by the client may have been full paid. Do you need to show the Client the outstanding balance against each purchase?
Or are you working toward producing a monthly statement of account? You require, at least, a balance at the start of the month, the sequential list of payments received and purchases made, and the outstanding balance at the end.

Hi Gap42,
The remarks is entered from a selection of a combo..

My data entry of buyer and their payments is a mainform and subform, so there is a link that identifies buyer and payments..

The main reason why i need this filtering is, i need to print those buyer (parcel of land/lot).. I need to filter those make a "Holding Fee)" holding is valid for 1 week from the holding fee date, after that date payment will "Forfeited" and that lot is open to sell again.. So i need to print a report to forward to the agents to notify their buyer about the forfeiture..

 

[GaP42]

This was provided to you:

SELECT BuyerID, COUNT(*) AS NumberPayments
FROM tblPayments
GROUP BY BuyerID
HAVING COUNT(*) <= 2

You have now said that you want to add criteria that only includes Payment records where "remarks" like "holding fee" and "1st monthly payment" occur (Post #6). However if a buyer has purchased the item, then payment records for that buyer will have Remarks: Holding Fee, 1st Monthly Payment, 2nd Monthly Payment, ... , Fully Paid? These buyer records will be included in your query if the criteria are simply that Remarks = Holding Fee or 1st Monthly Payment. Again, how will you ensure you do not include Buyers who have completed payments?

You now need to add a WHERE clause with those constraints to the above. If you need to learn about SQL: https://www.w3schools.com/sql/
Or use the Access Query Design

However your post #10 indicates the payment is "forfeited" - do you manually flag this? or do you run a query which determines / sets this value on the record?
NB: The query for Notice of Forfeiture (#10) and the query for Incomplete Purchase (#6) are not the same.

If manual, then would you not generate the notice for the agent/buyer at the time of editing? If a daily automatic routine, then you have a basic query that identifies records that are changed to forfeited and these are the records for which agents/buyers need to be given the advice
You may need to signify that a notification has been generated / issued to the agent / buyer for the purchase previously so that they are not included in a subsequent process to generate the notice (you can unflag it again if you need to re-issue).

A buyer may be purchasing more than one parcel of land/lot even in the same period of time. How do you distinguish these purchases?

 

[moi]

This was provided to you:

SELECT BuyerID, COUNT(*) AS NumberPayments
FROM tblPayments
GROUP BY BuyerID
HAVING COUNT(*) <= 2

You have now said that you want to add criteria that only includes Payment records where "remarks" like "holding fee" and "1st monthly payment" occur (Post #6). However if a buyer has purchased the item, then payment records for that buyer will have Remarks: Holding Fee, 1st Monthly Payment, 2nd Monthly Payment, ... , Fully Paid? These buyer records will be included in your query if the criteria are simply that Remarks = Holding Fee or 1st Monthly Payment. Again, how will you ensure you do not include Buyers who have completed payments?

You now need to add a WHERE clause with those constraints to the above. If you need to learn about SQL: https://www.w3schools.com/sql/
Or use the Access Query Design

However your post #10 indicates the payment is "forfeited" - do you manually flag this? or do you run a query which determines / sets this value on the record?
NB: The query for Notice of Forfeiture (#10) and the query for Incomplete Purchase (#6) are not the same.

If manual, then would you not generate the notice for the agent/buyer at the time of editing? If a daily automatic routine, then you have a basic query that identifies records that are changed to forfeited and these are the records for which agents/buyers need to be given the advice
You may need to signify that a notification has been generated / issued to the agent / buyer for the purchase previously so that they are not included in a subsequent process to generate the notice (you can unflag it again if you need to re-issue).

A buyer may be purchasing more than one parcel of land/lot even in the same period of time. How do you distinguish these purchases?

Hello,
All the flaging is done manually after getting a feedback from agents..

Yes it includes those buyers as you mentioned.. But those having status "holding fee" 1 wk from the payment date and getting a feedback from agent, will be flag manually "forfeited"..

Possible that same buyer will purchase another parcel/lot but it will be treated different from that forfeited lot for personal reason.
I hope i will learn the access sql.

 

[ebs17]

Yes it includes those buyers as you mentioned.. But those having status "holding fee" 1 wk from the payment date and getting a feedback from agent, will be flag manually "forfeited"..

All of these facts should be stored in tables and thus be able to be evaluated. So show the database schema (representation of the tables with their relationships). Knowing this is always the starting point of a query.

You also have to take into account that SQL performs set operations => everything at once. So you can't keep adding requirements and expanding the query. It may be that an additional requirement has to change the entire query completely.

So if the query is supposed to solve everything at once, all of the requirements must be known immediately in order to be incorporated as a solution. Telling vague stories alone may be informative, but it is not enough to suggest a solution.

Ideally, you should upload a sample database with the tables involved, including relationships, and a few meaningful records here. The example records should cover all conceivable cases. In addition, a representation of the desired result, which should EXACTLY result from the data provided.

 

[GaP42]

Hello,
All the flaging is done manually after getting a feedback from agents..

Yes it includes those buyers as you mentioned.. But those having status "holding fee" 1 wk from the payment date and getting a feedback from agent, will be flag manually "forfeited"..

Possible that same buyer will purchase another parcel/lot but it will be treated different from that forfeited lot for personal reason.
I hope i will learn the access sql.

@moi - as @ebs17 has indicated the db schema you are using is needed to provide solid advice. A sample db would be better.
So, "Remark" (or is it status?) of the records of interest have the text "Holding Fee" associated. It is also required that those records have a Payment Date > 1 week from the current date. These rules need to be part of your WHERE clause: WHERE Remark = "Holding Fee" AND Datediff("ww", PaymentDate, Today()), > 1 as one possible construct. It also probably has to exclude records flagged as "Forfeited". See point 2 below

I do not understand how to translate other things you have said into my guess about your database:

1. If the same buyer may be associated with payments for more than one lot, then a field is needed to distinguish the purchases. They are not currently treated differently in the query. A field (PropertyID?) eneeds to be included that allows them to be separately considered in the query.
2. Is the Remark or Status changed to "Forfeited" or is this a different field in your manual process? Are you expecting to set this before or after the query. If Before, then perhaps you only need to use this value in the WHERE statement, together with the PaymentDate being more than one week ago.

Post a sample of your db as Eberhard suggested.