calculated field

[jamesT]

This is the problem,

I have a database that is used by sales reps to add orders and produce invoices for customers.

A salesman earns commission on every sale

(This rate is defined in the salesman table)

to work out the total value of the invoice I did a calculated field,

To work out the commission for each salesman, I have a main form which shows all the salesman. A sub form which shows all the invoices

I am trying to reproduce the calculated field i.e. the total value of the invoice, but if I add the tables to do this I get a record for each item on the order, I tried to reference the field form the existing form but because i am using an unbound text box in the sub form, the same value filters down all the records.

not sure how to do this, once I have the total invoice field I can then work out the commission

Any help or pointers would be great thanks

 

[RV]

Why not using a report?

I assume that per order the salesman is registrated.
Create a query which totalizes the order amount per salesman.
Also combine this query to the commission rate per salesman.

Base your report on the query.

If you still want to use forms, you better base your forms on queries.

RV

 

[Pat Hartman]

1. When the order is placed, you should store the commission pct on the order record. This is not a violation of 2nd normal form. You need to store the rate at the time the order was placed since the rate may change over time which would prevent you from calculating accurate commission amounts on old orders unless you kept a history of commission rates by salesman. Keeping a record of each commission change for an employee is technically the "better" method. However, it is also the hardest to use because of the complications it causes in queries. So storing the commission rate in the order record is acceptable for almost all situations.

2. If you calculate the order total in the query rather than in the form, each subform row will be able to show a different order total. You can also calculate the commission in the query. These calculated fields will not be updateable but if you update one of their dependencies, they will change.