Calculated field

[Cesar]

I'm new using Access, so this may be pretty easy but I can figure outhow to
do it.

I need to calculate a new field (sales commisions) just multiplying the
sales value times the percent of commission, the problem is that the percent
of commision is not a value in a table as the sales value is; this percent of
commission is a changable value and actually I would like to be ask what is
that percent every time I run the query

Thanks

 

[KARL DEWEY]

Sales Commission: [sales value] * [Enter percent of commission like 11.9
]/100

 

[Jeff Boyce]

Cesar

It sounds like you are asking for a way to prompt the user for a percentage
(of commission) whenever the query runs.

You can use a "parameter prompt" (see Access HELP) to do this by putting
something like:

[Percent of Commission]

in the calculated field -- when Access can't find this, it uses it to
prompt.

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[fredg]

I'm new using Access, so this may be pretty easy but I can figure outhow to
do it.

I need to calculate a new field (sales commisions) just multiplying the
sales value times the percent of commission, the problem is that the percent
of commision is not a value in a table as the sales value is; this percent of
commission is a changable value and actually I would like to be ask what is
that percent every time I run the query

Thanks

All the records in the query will have the same % commission?

SalesCommission:[SalesValue] * [Enter the commission as a decimal]

Enter .06 when prompted if the commission rate is 6%

 

[Cesar]

Hi all,

Thanks for the answer, that's what exactly I meant. I created the field
using the brackets to ask me for the Percent of Commission, I worked exactly
the way I wanted,

Thank you very much,

Have a good day,
--
Cesar

Cesar

It sounds like you are asking for a way to prompt the user for a percentage
(of commission) whenever the query runs.

You can use a "parameter prompt" (see Access HELP) to do this by putting
something like:

[Percent of Commission]

in the calculated field -- when Access can't find this, it uses it to
prompt.

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm new using Access, so this may be pretty easy but I can figure outhow
to
do it.

I need to calculate a new field (sales commisions) just multiplying the
sales value times the percent of commission, the problem is that the
percent
of commision is not a value in a table as the sales value is; this percent
of
commission is a changable value and actually I would like to be ask what
is
that percent every time I run the query

Thanks

 

[Cesar]

Thanks Fred,

So far yes, all the records will have the same % of commission, but just for
curiosity, what do I need to do if the % of commission depends on the Sales
value, let's say above 10,000 the percentage will be 7% and below the 10,000
the percentage will be 6.5%?

When I run the query it prompts me for the commision rate
--
Cesar

I'm new using Access, so this may be pretty easy but I can figure outhow to
do it.

I need to calculate a new field (sales commisions) just multiplying the
sales value times the percent of commission, the problem is that the percent
of commision is not a value in a table as the sales value is; this percent of
commission is a changable value and actually I would like to be ask what is
that percent every time I run the query

Thanks

All the records in the query will have the same % commission?

SalesCommission:[SalesValue] * [Enter the commission as a decimal]

Enter .06 when prompted if the commission rate is 6%

 

[KARL DEWEY]

If you do not enter data at the prompt but just press ENTER it will calculate
commission based on sales volume.
Sales Commission: IIF([Enter percent of commission like 11.9 ] Is Not
Null, [sales value] * [Enter percent of commission like 11.9 ]/100,
IIF([sales value] <10,000, [sales value]* .065, [sales value]* .07))

--
KARL DEWEY
Build a little - Test a little

Thanks Fred,

So far yes, all the records will have the same % of commission, but just for
curiosity, what do I need to do if the % of commission depends on the Sales
value, let's say above 10,000 the percentage will be 7% and below the 10,000
the percentage will be 6.5%?

When I run the query it prompts me for the commision rate
--
Cesar

I'm new using Access, so this may be pretty easy but I can figure outhow to
do it.

I need to calculate a new field (sales commisions) just multiplying the
sales value times the percent of commission, the problem is that the percent
of commision is not a value in a table as the sales value is; this percent of
commission is a changable value and actually I would like to be ask what is
that percent every time I run the query

Thanks

All the records in the query will have the same % commission?

SalesCommission:[SalesValue] * [Enter the commission as a decimal]

Enter .06 when prompted if the commission rate is 6%

 

[Cesar]

Don't know if this is a duplicate but I don't know if the previous went thru,
Thanks, very helpul. Now here it is the real problem, It did work perfect in
Access but now I'm in EXCEL using a Microsoft Query to pull data from a SQL
database. The scenario is the same, need to calculate the new field by
multiplying the sales values times the % of commission which should be asked
every time I run the query. I tried in the same way creating the new field
but I got a message that says Invalid column name, If a use a constant, it
runs perfect, but since that value is going to change how can I make the
query to ask me for this % every time I run it?
Remember I'm in escel using microsoft query

Thanks again
Cesar

If you do not enter data at the prompt but just press ENTER it will calculate
commission based on sales volume.
Sales Commission: IIF([Enter percent of commission like 11.9 ] Is Not
Null, [sales value] * [Enter percent of commission like 11.9 ]/100,
IIF([sales value] <10,000, [sales value]* .065, [sales value]* .07))

--
KARL DEWEY
Build a little - Test a little

Thanks Fred,

So far yes, all the records will have the same % of commission, but just for
curiosity, what do I need to do if the % of commission depends on the Sales
value, let's say above 10,000 the percentage will be 7% and below the 10,000
the percentage will be 6.5%?

When I run the query it prompts me for the commision rate
--
Cesar

On Mon, 27 Oct 2008 10:15:01 -0700, Cesar wrote:

I'm new using Access, so this may be pretty easy but I can figure outhow to
do it.

I need to calculate a new field (sales commisions) just multiplying the
sales value times the percent of commission, the problem is that the percent
of commision is not a value in a table as the sales value is; this percent of
commission is a changable value and actually I would like to be ask what is
that percent every time I run the query

Thanks

All the records in the query will have the same % commission?

SalesCommission:[SalesValue] * [Enter the commission as a decimal]

Enter .06 when prompted if the commission rate is 6%

 

[KARL DEWEY]

I got a message that says Invalid column name,
I am not familiar with Excel queries. Just what did you use for column name?

 

[Cesar]

Karl,

The column name is actually the equation, Sales * [% of commission]

 

[John W. Vinson]

I'm new using Access, so this may be pretty easy but I can figure outhow to
do it.

I need to calculate a new field (sales commisions) just multiplying the
sales value times the percent of commission, the problem is that the percent
of commision is not a value in a table as the sales value is; this percent of
commission is a changable value and actually I would like to be ask what is
that percent every time I run the query

Thanks

In your Query you can put an expression in [square brackets] to generate a
prompt; i.e. you can have an expression

Comm: [SalesValue] * [Enter commission rate as fraction, e.g. .08 for 8%:]

If you want to enter 8 instead of .08, prompt accordingly and divide the
result by 100.

 

[KARL DEWEY]

Try changing it to Sales Times Commission

--
KARL DEWEY
Build a little - Test a little

Karl,

The column name is actually the equation, Sales * [% of commission]
--
Cesar

I am not familiar with Excel queries. Just what did you use for column name?