Show Sum Total Dollar Amount of All Records from one table in another table
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