Updating tables in access

The first one is NEVER use a combination of linked tables with local tables in your queries.

Instead download the server side tables as temp tables in your app or upsize your local tables to SQL Server and do the processing on the server in it’s entirety.

My database uses a form to add, edit and delete records from a table called Products.

Product ID is linked to the Product ID field in the Products table in a one to many relationship.

There are circumstances where storing a calculated result makes sense - typically where you need the flexibility to store a different value sometimes.

Say you charge a construction fee that is normally an additional 10%, but to win some quotes you may want to waive the fee. In this case it makes perfect sense to have a record where the fee is

Product ID is linked to the Product ID field in the Products table in a one to many relationship.There are circumstances where storing a calculated result makes sense - typically where you need the flexibility to store a different value sometimes.Say you charge a construction fee that is normally an additional 10%, but to win some quotes you may want to waive the fee. In this case it makes perfect sense to have a record where the fee is [[

Product ID is linked to the Product ID field in the Products table in a one to many relationship.

There are circumstances where storing a calculated result makes sense - typically where you need the flexibility to store a different value sometimes.

Say you charge a construction fee that is normally an additional 10%, but to win some quotes you may want to waive the fee. In this case it makes perfect sense to have a record where the fee is $0 instead of 10%, so you must store this as a field in the table.

To achieve this, use the After Update event of the controls on your form to automatically calculate the fee: Now whenever the Quantity or Unit Price changes, Access automatically calculates the new fee, but the user can override the calculation and enter a different fee when necessary.

If you are concerned about how a user could enter the calculated total with this arrangement, see Enter text in calculated controls.

||

Product ID is linked to the Product ID field in the Products table in a one to many relationship.There are circumstances where storing a calculated result makes sense - typically where you need the flexibility to store a different value sometimes.Say you charge a construction fee that is normally an additional 10%, but to win some quotes you may want to waive the fee. In this case it makes perfect sense to have a record where the fee is $0 instead of 10%, so you must store this as a field in the table.To achieve this, use the After Update event of the controls on your form to automatically calculate the fee: Now whenever the Quantity or Unit Price changes, Access automatically calculates the new fee, but the user can override the calculation and enter a different fee when necessary.If you are concerned about how a user could enter the calculated total with this arrangement, see Enter text in calculated controls.

]] instead of 10%, so you must store this as a field in the table.To achieve this, use the After Update event of the controls on your form to automatically calculate the fee: Now whenever the Quantity or Unit Price changes, Access automatically calculates the new fee, but the user can override the calculation and enter a different fee when necessary.If you are concerned about how a user could enter the calculated total with this arrangement, see Enter text in calculated controls.

instead of 10%, so you must store this as a field in the table.

To achieve this, use the After Update event of the controls on your form to automatically calculate the fee: Now whenever the Quantity or Unit Price changes, Access automatically calculates the new fee, but the user can override the calculation and enter a different fee when necessary.

If you are concerned about how a user could enter the calculated total with this arrangement, see Enter text in calculated controls.