Forum Discussion
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 Pers...
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.
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.