Data from from field not found in table

[Rachael]

I have a form Employees and a subform Policies, based on tables with the same
names. Records in the Policies table are attached to a record in the
Employees table using the employee's SSN as the unique identifier.

On the subform Policies, there is a display field 'Total' that sums the
premium amounts for all the policies for a given employee. This field is for
display only and is not linked to any field in the table Policies.

On the form "Employees' there is a field "Total Premium" that has as its
control source the amount displayed in the "Total" field on the subform
Policies. This field "Total Premium" is linked to a corresponding field in
the Employees table.

How can I get the total premium amount on the Employees form (attached to
the corresponding field in the Employees table) to show up in the table
itself?I need this number for reporting purposes. Am I overlooking something?

I'm running Access 2007.

Thanks,
Rachael

 

[Jeff Boyce]

Rachael

The key phrase here may be "for reporting purposes" ...

If you are saying that you'd like to be able to report (i.e., print)
information about Employees, including the total amount from all their
Policies, you do NOT need to store that total amount in the Employee form.
In fact, trying to do so will mean you will also have to create procedures
to ensure data integrity/synchronization. For example, what happens to the
total amount if one of the Policy amounts changes? And what happens to the
Policy amounts if someone alters the Total?!

Instead, use a query to sum-up the Policy amounts on-demand. Create a new
query, make it a Totals query, GroupBy EmployeeID, Sum on Policy Amount.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP