I have a customer who uses QV to display the amount owed to them by their clients. They have 3 tables that are used to calculate this; SALE -> PAYMENTMETHOD -> PAYMENT.
SALE joined to PAYMENTMETHOD using SaleID
PAYMENTMETHOD joined to PAYMENT using PaymentID
A problem arises when the payment method is set to Layaway and there is no initial payment, the debt is not displayed because there is no PaymentID. Is there a way to create a dummy record for the PAYMENT table if the PAYMENTMETHOD = 'Layaway'? As it currently stands, users have to populate the PAYMENT table with an initial value of £0.00.
I'm not sure I follow the question, these three tables are not linked to each other: SALES & PAYMENT are not directly linked, they are only linked to PAYMENTMETHOD.
The report takes the amount of money the sale was for, checks the payment method and then takes away the sum(payed_amount). But if there has been nothing paid, the total amount left to be paid isn't displayed as there is no PaymentID stored in the PAYMENT table that matched the PAYMENTMETHOD table.