Summary Fields and Table Relationships

[Texarado]

My file summarizes health insurance proposals for presentation to clients.

It has two tables: one contains policy descriptions and rates (Table "A") and another showing the client's employee census (Table "B").

The census (Table "B") has fields for employee name, employee age, employee sex and coverage: Employee-Only / Employee & Spouse / Employee & Children / Family (from a drop-down list.)

Table "B" also has summary fields that calculate the number of employees in each of these coverage classifications.

Table "A" has rates for each policy that will be multiplied by the number of people in each coverage classification (from the summary fields in Table "B".)

However, when I try to create the relationship between the tables, the summary fields in Table "B" are dimmed. Is there a way to create this relationship?

Thanks!

Don

[John Mark Osborne]

It's not possible to create a relationship on a summary field. If you tell us what you are trying to do (other than I want a relationship using a summary field as the key), maybe we can help.

[Texarado]

Thank you for your help. This should make sense of what I'm trying to do.

Table "B" will contain records for each employee. Fields for each employee with indicate if he or she is choosing coverage for one of four possibilities: Employee-Only, Employee & Spouse, Employee & Children or Full Family.

Summary fields for Table "B" will count the number of people choosing each of these classifications. For example:

Number of employees choosing EE coverage

Number of employees choosing ES coverage

Number of employees choosing EC coverage

Number of employees choosing Family coverage

Table "A" will contain records for various insurance plans. Fields within each of these records will contain the rates for each of the coverage options.

Rate EE

Rate ES

Rate EC

Rate Family

To caclulate the total cost, these rates must be multiplied by the number of people in each corresponding classification (in Table "A".) For example:

Cost EE = Rate EE * Number of employees choosing EE coverage

Cost ES = Rate ES * Number of employees choosing ES coverage

Cost EC = Rate EC * Number of employees choosing EC coverage

Cost Family = Rate Family * Number of employees choosing Family coverage

Total Cost = Cost EE + Cost ES + Cost EC + Cost Family

This is why I want to be able to use the summary results from Table "B" in my calculations Table "A".

If it's not possible to use summary results in table relationships, might there be another approach?

Thanks!

[The Shadow]

A single summary field in TableB can accomplish this, define it as Count of your primary key. If each employee record has a coverage field containing EE, ES, etc., and for each rate record (TableA) you have a coverage field, and define a relationship where the TableA.coverage = TableB.coverage. Now whenever you access TableB's summary field from TableA, you will get only the related records counted, which will be the number of employees using that type of coverage.

[Mikhail Edoshin]

You'd better think about it other way Instead of counting employes with same coverage and then multiplying them to the rate you can simply write down the rate for every employee and then add the results together to get the total cost. This means that you should add a field in your Employee table to store the coverage rate from the related table. You can get make the field calculated or looked up; you probably need the latter.

Then make a summary field Total of Rate to add up the contents of the field. You can also sort the found set by coverage type and get subsummaries by each type, if you wish.