The simplest way to do this is to produce a report from the Sales table, summarized by customer and by year.
BTW, I'd suggest putting both tables in the same file, unless you have some reason not to.
Thanks for the response, however this is not quite what I want. I can already pull this information in a sales report as suggested but I want something more dynamic.
I have to keep separate customer and sales files as the data comes from two sources and is updated regularly. To combine them is impractial. The key point, which I may have not made clear, is that I want to flip through customer records in browse mode and see the annual sales fields update for each company I look at.
I could present the customer data in the Sales browse which may help. Maybe a self-relationship in Sales to total the customer sales records by year would work, listing 2007, 2008 and 2009 sales per company and placing contact info in a portal?
I want to flip through customer records in browse mode and see the annual sales fields update for each company I look at.
That too is possible, just more complex. The problem here is that you want to see only one record per year in the portal to Sales.
I am not sure I can explain the solution within the confines of a forum message, so I'll refer you to these:
Another option, a somewhat simpler one, is to use a table of years with a global gCustomerID field to summarize the sales values by year for the selected customer - but you need to keep the global synchronized to the currently viewed customer. This is easy to do in version 10, but in previous versions it means restricting the user to scripted navigation.
How would this table of years/gCustomerID field work? Scripted navigation is not a problem as I am the main user of the database
Here's a simplified example:
1. Create the Years table with the following fields:
• Year - Number, Auto-enter serial number starting at, say 2005 (the earliest year for which you'll have data)
• gCustomerID - global (same type as your CustomerID in the Customers table)
2. Define these two relationships (using two occurrences of the Years table):
Years::Year = Sales::Year
Years::gCustomerID = Sales::CustomerID
Customers::CustomerID x Years 2::Year
3. Add a calculation field cSum to the Years table (result is Number) =
Sum ( Sales::Amount )
4. Place a portal to Years 2 on a layout of Customers, showing the Year and cSum fields.
Thanks - this is a great solution!