Unanswered: Can't get calculation - problem with table relationship?
I have a relational db with three main tables - Customers, Bookings and Payments. Customers on the 'one' side, both other sides 'many'. I have now made an input form (main form Customers) with subforms for both Bookings and Payments, which are in datasheet view and working nicely. I added another subform to show the Total of Bookings - Total of Payments for each customer (the balance of account) which is now causing a problem.
Because if the customer had made no payments, then the record does not exists. The record only exists if the customer has made bookings, and then made payments, not if he never made payments. Which is, of course, the very situation that the database needs to find.
I fear I have made some fundamental mistake in the structure of the db, which would be bad because I have imported 979 records and really thought I'd got it right this time. Please help!
Actually, the job could be done by a calculated control on the main form, bringing data from the subforms. I have set up the calculated fields on the subforms, which are working fine, but I can't show these amounts on the main form - it returns a 'Name?' error.