How to update fields automatically

[kosan]

Good Moring,

I have a table named serviceCall that has a many-to-many relationship with a table named employee through a table named employeeServiceCall that stores the employeeId and the serviceCallId both combined comprise the primary key of the intermediary table employeeServiceCall.

I have 2 questions,

first,

When a user uses the form frmServiceCall to insert or update a service call how can I retrieve the amount of employees involved in that specific record as a number and store it in a field named amountOfEmployees located in the serviceCall table in order to use it for future processing?

second,

When a user uses the above form to insert a new record, how can increment by 1 the number field named amountOfCalls located in the table named customer?

FYI the customer table is joined with the serviceCall table through the customerId field.

Thank you for your help!
Kostas

[HiTechCoach]

Kostas,

Welcome!

I have a table named serviceCall that has a many-to-many relationship with a table named employee through a table named employeeServiceCall that stores the employeeId and the serviceCallId both combined comprise the primary key of the intermediary table employeeServiceCall.

I have 2 questions,

first,

When a user uses the form frmServiceCall to insert or update a service call how can I retrieve the amount of employees involved in that specific record as a number and store it in a field named amountOfEmployees located in the serviceCall table in order to use it for future processing?

You don't.

You should NOT store calculated values that as based on the data. You perform the calculation as need to get the value. This way it is always accurate.

I would use a totaling query to get the count. This query can be added to any other query to get the value you want.

second,

When a user uses the above form to insert a new record, how can increment by 1 the number field named amountOfCalls located in the table named customer?

You don't.

You should NOT store calculated values that as based on the data. You perform the calculation as need to get the value. This way it is always accurate.

I would use a totaling query to get the count. This query can be added to any other query to get the value you want.