Jump to content

Summary Fields and Table Relationships


Texarado

This topic is 6439 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 6439 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.