on-the-fly calculation based on query

[danikuper]

Hi!

I have a database for sales orders with tables like:

tblOrder
tblRep
tblRep-Order
tblQuota
tblRep-Quota

Each sales rep is linked to an order (via tblRep-Order). Each rep also has a monthly quota (tblRe-Quota). Besides his/her quota, the tblRep-Quota also has a field for Attainment, that is, how close to the quota that rep is.

I've created a query that based on a month selected from a combo-box gives me:
Rep, Total$, Quota

What I'd like to do is as the query is run, to calculate and update the attainment field (total$/quota) so I'd get as result of the query (shown in my form) the: Rep, Total$, Quota, Attainment.

Is there an easy way to accomplish that?

any help is greatly appreciated!!!!

 

[roelandkim]

Since the field attainment is a calculated field, meaning it is dependend on outside variables to get a result, there is no reason to store it. Just calculate it in the query. You can than bind your form the the query and show the results, whenever the base variables are changed, the calculation will be updated.

Try this, in your query type this in a column:
Attainment: [total$]/[quota]

Now when ever you change either total$ or quota the calculation will reflect the changes.

 

[danikuper]

Humm... makes sense.

I tried that however something strange is happening. The Total$ field in this query is in fact a sum (Sum Of Total$: Total$) and so I had in my query: Attainment: [Sum of Total$]/[Quota] but every time the query is run, it pops up a dialog box asking for Sum of Total$.

What's happening?

thanks in advance!!

 

[neileg]

I'm not sure quite what you mean about the Total$ fiedl and the Sum of Total$.

However, I think the calculation should be done in the form. You have a control holding the $ figure and another control for the quota. You need to add an unbound control that has as its source = [Name of control holding $ value]/[Name of Control holding quota]

 

[Rich]

Or use another query based on the first

 

[danikuper]

Thanks Neil.

You were right.. having the calculation done on the form works and it makes sense.

Another question I have related to that is the following:

My query looks for orders (and their totals) based on a "month" and "year" combo-boxes in my form. If I want to display not only the totals for the month/year with the quotas and attainment but also their totals year-to-date, how do I do that?

thanks again

 

[neileg]

Sorry, I'm off for a weeks holiday so no time.

I suggest you look at DSum().