Monthly payment query

[karenlorr_uk]

A strange query question.

Let’s say I have a table containing people’s details:

PersonID = 1
First_Name = Elizabeth
Surname = Windsor

PersonID = 2
First_Name = Philip
Surname = Windsor

PersonID = 3
First_Name = Charles
Surname = Windsor

Etc.

I send each person an invoice at the start of the finical year (01/04/21) for £12 and say they can pay it monthly, at £1 per month.  This arrangement could continue for many years.

I have another table to record to record the payments
ID, Date_paid, Amount, PersonID

So here is the question.

What would the query look like to show who was up to date with their monthly payments and who wasn’t? 

I have been trying many different options and was wondering if anyone could help steer me in the right direction.

Thank you

[#2]

You need three tables not two:
Person
Invoice
InvoicePayment

Record each invoice with the InvoiceDate and the PersonID for whom it was created.

Record each invoice payment with the InvoicePaymentDate and the InvoiceID.

Query for the most recent InvoicePaymentDate in the invoice payment date and the corresponding Invoice date. Compare that MostRecentInvoicePaymentDate to the current date.

[#3]

@karenlorr_uk 

Also, how do you calculate the due date for each installment? How do you know when they are late?