I have two tables, both with records identified by policy number (this is insurance). The two tables show different information about the policies, and have almost the same collection of policy numbers. They are joined by this policy number.
Even though the tables show different information for each policy, some fields are common. In particular, each policy is associated with a year. Sometimes for a given policy, the year in one table doesn't match the year in the other table.
I would like to make a chart, or really a table that displays this for me. I was thinking of 4 columns.
- column 1 would be a year.
- column 2 would be the count of policies in table 1 that have that year.
- column 3 would be the count of policies in table 2 that have the year that is gotten by linking the policy to table 1.
- column 4 would be the count of policies in table 2 that have the year that is coded into table 2.
Is this possible? If so, can anyone tell me how to do this? I can do columns 1-3 without any problem. To do that my column 1 is taken from Table 1. I don't know how to group the policies in Table 2, by the year coded to Table 2 and then match it with the proper row in column 1.
Thanks for any help.
Mitch
