Creating formula in Access

[Ireland1978]

I've searched through the forum and can't find anything that helps me.
I'm trying to get a field in a Sales Rep form that would calculate commission earned by a sales rep, based on how many customers they have.
I just want to use something basic like numOfCustomers * 50.
Where do I actually put in this formula? In the query criteria or in the table design view?

Thanks

 

[Remou]

In a form, you could say something like:
=DCount("*","Customers","RepID"=[RepID])
Is this what you mean?
Whether this is easier than using a query depends, I think, on how your query is set up.

 

[dhookom]

Actually, the DCount() would look more like:
=DCount("*","Customers","RepID=" & [RepID])
This assumes a table named Customers and a numeric RepID field in the table.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Remou]

I could have sworn I corrected that before posting.

 

[Ireland1978]

Thanks for the replies posted above.

I've tried the following:

UPDATE Sales_Reps SET Commission =
(SELECT EmployeeNumber, COUNT(*) AS ["Number of Customers"]
FROM Customers
GROUP BY EmployeeNumber) * 50;

But I get the following message box:
'You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field'

Any ideas??? I haven't a clue!

 

[PHV]

Something like this ?
UPDATE Sales_Reps
SET Commission = 50 * (SELECT COUNT(*) FROM Customers WHERE EmployeeNumber = Sales_Reps.[name of employee# field])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Ireland1978]

Thanks again.
When I run this query, a text box pops up and I have to enter a parameter for commission.

 

[PHV]

Does the Sales_Reps table have a field named Commission ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Ireland1978]

It does, but there's nothing in it yet as I want it to be generated. I planned on having a button to click once the current sale amount is entered, then the commission would be calculated (via a macro)

 

[dhookom]

UPDATE Sales_Reps SET Commission =
DCount("*", "Customers","EmployeeNumber=" & EmployeeNumber) * 50;

The above assumes a numeric EmployeeNumber. If it is text, try:
UPDATE Sales_Reps SET Commission =
DCount("*", "Customers","EmployeeNumber=""" & EmployeeNumber & """") * 50;


Do you do this on a quarterly basis? I'm not sure that I would store a value like this that might keep changing.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]