I know how to place a simple chart on a form and have it display summary information. But what if I need more sophistication, such as showing information that is related, but not within the current bound record or table?
This solution applies to Access 2003 and earlier versions. At the end of the recipe, you'll find comments about charts in Access 2007.
Let's start with the basics. Access has a basic chart control that is bound to a form via a parent and child key, in the same manner that a subform is in sync with the parent form on which it's placed. In Access 2003 and earlier versions, you add a chart to a form using the Insert → Chart menu command.
Figure 6-29 shows a form based on a table of customers. The Record Source of the form is the tblCustomers table. The chart is bound to the Customer records, but the Row Source of the chart is this SQL statement, based on the tblAnnualSales table:
SELECT tblAnnualSales.yr, tblAnnualSales.Amount FROM tblAnnualSales;
Figure 6-29. A form with a bound chart
Figure 6-30 shows the two tables. The tables relate in the CustomerID field. This field isn't included in the SQL statement; it isn't necessary because the chart and the form are CustomerID-related in the Link Child Fields and Link Master Fields properties.