Forum Discussion

karenlorr_uk's avatar
karenlorr_uk
Copper Contributor
Apr 06, 2021

Monthly payment query

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

 

 

 

access

2 Replies

  • George_Hepworth's avatar
    George_Hepworth
    Silver Contributor
    Apr 06, 2021
    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.