3 Replies Latest reply on Jan 3, 2011 12:15 PM by philmodjunk

    Show Sum Total Dollar Amount of All Records from one table in another table

    MiloMV

      Title

      Show Sum Total Dollar Amount of All Records from one table in another table

      Post

      I am sorry if this has been covered already but I cannot find the answer. My database has two tables. CUSTOMERS (which has contact information) and PURCHASES (which has three fields: PurchaseDate, Amount, and a match field with the CUSTOMERS table).

      I have it set up so there is a portal on the CUSTOMERS table that calls in a list of all the purchases made by the customer whose record is being displayed. I also have a calculation field under the portal which gives a total amount for all the purchases of that customer as displayed in the portal using the formula: Sum (Purchases::Amount).

      I would like to add a field box on the CUSTOMERS table showing the grand total of all the purchases in the PURCHASES database. In other words, for each customer record displayed, in addition to having that customer's purchases and indivudual total displayed (as I have now), I would also like a box on each record that will tell me the grand total of all the amount fields in the PURCHASE table. So when I look at any customer record I will be able to see at a glance how much that customer has bought from me and the total amount I have made from all customers. Just as the the calculation field for the customer under the portal automatically updates as I add a new purchase into the portal, I would like the grand total shown on the record to update as well.

      I tried making a summary field in the PURCHASES table which is the "total of" the amount field. In the PURCHASES table, this field shows the grand total of all the records (which is the figure I want). But when I add this field to my CUSTOMER table layout, it only shows the total of that customer's purchases.

      Is it possible to do what I am asking?

      Thank you in advance. Milo

        • 1. Re: Show Sum Total Dollar Amount of All Records from one table in another table
          philmodjunk

          You need to add a new relationship to what you already have.

          Open Manage | Database | Relationships.

          Click Purchases to select it, then click the button with two green plus signs to make a new table occurrence of purchases.

          Link this new table occurrence to customers like this:

          Customers::CustomerID x Purchases 2::CustomerID

          To use x instead of =, double click the line linking these two Table Occurrences and use the drop down to change the = to x. This operator matches all records in Customers to all records in Purchases 2.

          Now you can add the summary field to your customers layout by selecting it from Purchases 2 or you can define a calculation field in customers as Sum ( Purchases 2::Amount )

          If Table Occurrence is a new term, you might want to read this thread to learn more:  

          Tutorial: What are Table Occurrences?

          • 2. Re: Show Sum Total Dollar Amount of All Records from one table in another table
            MiloMV

            Dear PhilModjun,

            Thank you for this precise and easy to follow answer. There was one slight glitch that I worked through, but I am not sure it is the proper way to do it. I forgot to mention that I am not using a single field (like an ID#) as the match but am using two fields to make the match: Each table has the fields, Firstname and Lastname and this is how I formed the relationship in the original Table Occurances connecting both fields with an "=".

            I did everything you said (but using both fields for the match between CUSTOMERS and the new PURCHASES 2) but I could not get the = in the connecting line to turn to an x. It made an x with the sides closed like an infinity sign. With this configuration, a summary or calculation field using PURCHASERS 2 still only showed each customer's individual total. In the "Edit relationship window it showed:

            Customers       Purchases 2

            Firstname     x     Firstname

            Lastname     =     Lastname

            I could not find a way to connect the Lastnames also with a "x" so I decided to try deleting the link between the Lastnames (leaving only the link between the Firstnames) and this worked as you said it would.

            I guess my question is this: Is this the proper way to acheive my goal and is the integrety of the database ok? Or should I be using both the Firstname and Lastname fields as relationship matches and find a way to make both fields display an "x."

            Thank you again for your help in this. Milo

            • 3. Re: Show Sum Total Dollar Amount of All Records from one table in another table
              philmodjunk

              Keep in mind that this is a separate relationship to be added to what you already have. You shouldn't modify any that you've already created as that could affect the way parts of the system that you've already defined work. That said, there's no need for more than one pair of fields when you use the x operator as this operator matches all records from purchases with all records from customers. Any additional field pairs wouldn't make sense here.

              Lastly, I strongly recommend that you dispense with using name fields to link customers to purchases. Names are not unqiue, they get changed from time to time and sometimes you enter a name incorrectly and don't discover the error right away. In all of those cases, changing the name fields in customers will disconnect your purchases records from that record. Define a serial number field in Customers and link it to a number field in Purchases to avoid all of those issues.