Results 1 to 3 of 3
  1. #1
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90

    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!

  2. #2
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90
    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.

  3. #3
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90
    Solved by adding a '0' payment record for each customer that doesn't have any.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •