1 Reply Latest reply on Nov 29, 2008 1:20 PM by jsalzer_1

    multiple items, multiple payments (many-to-many join?)



      multiple items, multiple payments (many-to-many join?)


      Muddled newbie asks:


      A gallery sells individual works of art on consignment at an annual auction (auction table)


      Consignors are in a separate Consignors address table.


      Consignors are paid after the auction, so checks cover some, but not necessarily all of the works they own (payments table).


      How can I specify which works are being paid for with an individual check?

      How can I show when a check is only a partial payment for one work?




        • 1. Re: multiple items, multiple payments (many-to-many join?)

          My immediate thought would be for a "PaymentBreakout" table with 3 fields:





          Link this in a one-to-many with the AuctionItems and Payments tables.


          When entering a payment in the Payments table, set up a button you can click to add a new "PaymentBreakout" record and auto-enter the payment's ID#.  Here, you record the ID# for the item and the amount applied toward that item.  Repeat as necessary for any other items covered by the check.


          This allows you to make partial payments toward multiple works with one check.  It also makes it possible to have one check going toward multiple artists.  It has the added benefit of providing you with a way to double-check your math.  You can have the screen turn red if the sum of the PaymentBreakouts is not equal to the total check amount.


          To get from the consignor to the amount collected for him or her, you'd go through his or her items. 


          Hope that helps.