Calculation for related fields in the same record

[candersoncscs]

I am creating a database for a personal training business. I have a "Trainer Worksheet" table. The records for that table look something like this:

Contract# Total Sessions Rate Per Session

A1(field) B1 (field) C1 (field)

A2(field) B2 (field) C2 (field)

The "Trainer Worksheet" table is related to a "Contracts" table through a "Trainers Code" field. When the Trainers Code is entered into the "Trainers Worksheet" record, a value list is created in A1 with a list of contracts that are assigned to that trainer (via Trainer Code).

What I need to have happen is that when a contract value is selected in A1, B1 automatically is entered with the data from the contract A1 specifies.

So, say contract # 102 is entered into A1. I would need B1 and C1 to reference contract # 102 and enter the total # of sessions and rate per session from that contract. Each contract is a different record in the "Contracts" table.

Is there any way to do this with a calculation?

[LaRetta]

Hi candersoncscs, welcome to FM Forums!

You would establish a relationship (if you don't already have one), joining Trainer Worksheet::Contract# to Contracts::Contract# using [color:blue]=. Then, within Trainer Worksheet, you would go to the options (in field definitions) for Total Sessions and, under the auto-enter tab, select 'calculated value'. In the calculation dialog above left, find your Contracts table from the pop-up and double-click the Total Sessions field. Repeat for the Rate Per Session field. You could also use the auto-enter Lookup feature but I prefer auto-enter when possible.

There is one thing ... if a Contract can't change once established and if you always create NEW contracts for future work, then you really don't need to duplicate the information within Trainer Worksheet - you can simply place the Contract::Total Sessions and Contract::Rate Per Session field directly on your Trainer Worksheet form and it will display the correct value (after they are properly related). If there is a possibility of changing anything within a contract and you want any prior Trainer Worksheet records to retain their original value then you will need those fields within Trainer Worksheet.

LaRetta :wink2:

[candersoncscs]

Hi LaRetta,

Thank you very much for your response...everything worked out just great! I've also found many of your other posts to be quite helpful.

After looking around at different software options for my business, I decided to try to make something of my own that made sense to me and did what I needed it to do. I've only been at it a week, but with the help I've gotten from reviewing the posts in this forum it's been relatively smooth for this newbie.

Thanks again...I appreciate it!