expression i need to write rate -admin costs*driver percentage in.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have three fields in a table rate, admin costs and driver percentage

1. Rate - currency
2. Admin Costs - number intger currency default 5
3. driver percentage - number intger currency default 25

I need an expression to have rate - admin costs * driver percentage.
Right now I have rate * driver percentage but I can not get it to deduct the
5% in admin costs. I need the driver percentage based on the rate minus
admin costs.

HELP!
 
dasad said:
I have three fields in a table rate, admin costs and driver percentage

1. Rate - currency
2. Admin Costs - number intger currency default 5
3. driver percentage - number intger currency default 25

I need an expression to have rate - admin costs * driver percentage.
Right now I have rate * driver percentage but I can not get it to deduct the
5% in admin costs. I need the driver percentage based on the rate minus
admin costs.
Click to expand...


Not sure what you're trying to do here. Is Admin Costs a
percent? Is Rate a dollar amount?

Maybe this is close??

Rate* (1 - [Admin Costs]/100) * ([driver percentage]/100)
 
Thanks for the help. It did not work. Rate is a dollar amount and the admin
costs is a percentage of that dollar amount.

Marshall Barton said:
dasad said:
I have three fields in a table rate, admin costs and driver percentage

1. Rate - currency
2. Admin Costs - number intger currency default 5
3. driver percentage - number intger currency default 25

I need an expression to have rate - admin costs * driver percentage.
Right now I have rate * driver percentage but I can not get it to deduct the
5% in admin costs. I need the driver percentage based on the rate minus
admin costs.
Click to expand...


Not sure what you're trying to do here. Is Admin Costs a
percent? Is Rate a dollar amount?

Maybe this is close??

Rate* (1 - [Admin Costs]/100) * ([driver percentage]/100)
Click to expand...
 
Could you provide additonal explanation or, at least, some
sample data and expected results? "It did not work." is
just not enough information for me to work with, or would
prefer that I keep making wild guesses as to what you want?
--
Marsh
MVP [MS Access]

Thanks for the help. It did not work. Rate is a dollar amount and the admin
costs is a percentage of that dollar amount.

Marshall Barton said:
Not sure what you're trying to do here. Is Admin Costs a
percent? Is Rate a dollar amount?

Maybe this is close??
Rate* (1 - [Admin Costs]/100) * ([driver percentage]/100)
Click to expand...
Click to expand...
 
Please clarify your post.

2) and 3): Are they number or currency? Surely you would not format a
field in which you store a percentage using Currency format?

You say you got 1) * 3) working. If the default for 3) is 25 then
presumably your formula is Rate * DriverPercentage / 100?

Anyway, basic formula is:
(Rate - (Rate * Admin Costs)) * DriverPercentage

Regards,
Andreas
 
i work at a truck line. i have a table for every load we book with the rate
for the load. From this table i have a query to pay the drivers for each
load (rate * driver percentage/100) In the design of the table rate is
currency, driver percentage is number, intger,and currency. Admin costs is
listed the same as the driver percentage. right now I have a report pulled
from the query to do a "payroll" sheet per driver. I need to deduct an
optional 5% admin costs from the rate prior to the driver percentage.
example: rate is $100.00 minus $5.00 for admin cost, the driver gets paid a
percentage of the $95.00 that is left.

If this does not help, I will be glad to send you a copy of the database if
you let me know how.

Marshall Barton said:
Could you provide additonal explanation or, at least, some
sample data and expected results? "It did not work." is
just not enough information for me to work with, or would
prefer that I keep making wild guesses as to what you want?
--
Marsh
MVP [MS Access]

Thanks for the help. It did not work. Rate is a dollar amount and the admin
costs is a percentage of that dollar amount.

dasad wrote:
I have three fields in a table rate, admin costs and driver percentage

1. Rate - currency
2. Admin Costs - number intger currency default 5
3. driver percentage - number intger currency default 25

I need an expression to have rate - admin costs * driver percentage.
Right now I have rate * driver percentage but I can not get it to deduct the
5% in admin costs. I need the driver percentage based on the rate minus
admin costs.
Click to expand...
Marshall Barton said:
Not sure what you're trying to do here. Is Admin Costs a
percent? Is Rate a dollar amount?

Maybe this is close??
Rate* (1 - [Admin Costs]/100) * ([driver percentage]/100)
Click to expand...
Click to expand...
Click to expand...
 
Andreas, I copied your formula but I received an error message that said I
had an invalid syntex.

FOr more infor see my reply back to Marshall
 
Original Solution:
(Rate - (Rate * Admin Costs)) * DriverPercentage

New Solution:
(Rate - (Rate * Admin Costs)) * Driver Percentage

Note the extra space.
Since you are breaking the rule of good naming conventions by using
spaces in your names you will need to include the square brackets:

([Rate] - ([Rate] * [Admin Costs])) * [Driver Percentage]

Also, you will need to divide by 100 where appropriate.

Regards,
Andreas
 
Sorry this did not work either. the query is still just performing rate *
driver percentage. For some reason it will not acknowledge the admin costs.

Andreas said:
Original Solution:
(Rate - (Rate * Admin Costs)) * DriverPercentage

New Solution:
(Rate - (Rate * Admin Costs)) * Driver Percentage

Note the extra space.
Since you are breaking the rule of good naming conventions by using
spaces in your names you will need to include the square brackets:

([Rate] - ([Rate] * [Admin Costs])) * [Driver Percentage]

Also, you will need to divide by 100 where appropriate.

Regards,
Andreas

Andreas, I copied your formula but I received an error message that said I
had an invalid syntex.

FOr more infor see my reply back to Marshall

:
Click to expand...
Click to expand...
 
How about that, my original guess was the right one ;-)

Now let's go back and try the expression I posted earlier
and see if we can figure out what about it, or your data,
leads you to think "it doesn't work".

Do you have a data record with a Rate of 100, Admin of 5 and
Dirver 25? Try it and see what result you get, it should be
23.75. If not, pleas post back with the result you did get
along with a Copy/Paste of the **exact** expression you're
using.

Check Andreas posts too. We are both suggesting equivalent
expressions that will produce the same results. He also
makes a good point about using names with spaces or other
funky characters.
--
Marsh
MVP [MS Access]


i work at a truck line. i have a table for every load we book with the rate
for the load. From this table i have a query to pay the drivers for each
load (rate * driver percentage/100) In the design of the table rate is
currency, driver percentage is number, intger,and currency. Admin costs is
listed the same as the driver percentage. right now I have a report pulled
from the query to do a "payroll" sheet per driver. I need to deduct an
optional 5% admin costs from the rate prior to the driver percentage.
example: rate is $100.00 minus $5.00 for admin cost, the driver gets paid a
percentage of the $95.00 that is left.
Thanks for the help. It did not work. Rate is a dollar amount and the admin
costs is a percentage of that dollar amount.


dasad wrote:
I have three fields in a table rate, admin costs and driver percentage

1. Rate - currency
2. Admin Costs - number intger currency default 5
3. driver percentage - number intger currency default 25

I need an expression to have rate - admin costs * driver percentage.
Right now I have rate * driver percentage but I can not get it to deduct the
5% in admin costs. I need the driver percentage based on the rate minus
admin costs.


:
Not sure what you're trying to do here. Is Admin Costs a
percent? Is Rate a dollar amount?

Maybe this is close??
Rate* (1 - [Admin Costs]/100) * ([driver percentage]/100)
Click to expand...
Click to expand...
Click to expand...
 
Driver Pay: [rate]*[driver percentage]/100
this is the expression I am currently using. I need to add in the admin
costs. I have "cut and paste" all the formulas you both have given me and it
still leaves out the admin costs. Instead of 23.75, I get 25.00.

I really appreciate the help on this.

Marshall Barton said:
How about that, my original guess was the right one ;-)

Now let's go back and try the expression I posted earlier
and see if we can figure out what about it, or your data,
leads you to think "it doesn't work".

Do you have a data record with a Rate of 100, Admin of 5 and
Dirver 25? Try it and see what result you get, it should be
23.75. If not, pleas post back with the result you did get
along with a Copy/Paste of the **exact** expression you're
using.

Check Andreas posts too. We are both suggesting equivalent
expressions that will produce the same results. He also
makes a good point about using names with spaces or other
funky characters.
--
Marsh
MVP [MS Access]


i work at a truck line. i have a table for every load we book with the rate
for the load. From this table i have a query to pay the drivers for each
load (rate * driver percentage/100) In the design of the table rate is
currency, driver percentage is number, intger,and currency. Admin costs is
listed the same as the driver percentage. right now I have a report pulled
from the query to do a "payroll" sheet per driver. I need to deduct an
optional 5% admin costs from the rate prior to the driver percentage.
example: rate is $100.00 minus $5.00 for admin cost, the driver gets paid a
percentage of the $95.00 that is left.
dasad wrote:
Thanks for the help. It did not work. Rate is a dollar amount and the admin
costs is a percentage of that dollar amount.


dasad wrote:
I have three fields in a table rate, admin costs and driver percentage

1. Rate - currency
2. Admin Costs - number intger currency default 5
3. driver percentage - number intger currency default 25

I need an expression to have rate - admin costs * driver percentage.
Right now I have rate * driver percentage but I can not get it to deduct the
5% in admin costs. I need the driver percentage based on the rate minus
admin costs.


:
Not sure what you're trying to do here. Is Admin Costs a
percent? Is Rate a dollar amount?

Maybe this is close??
Rate* (1 - [Admin Costs]/100) * ([driver percentage]/100)
Click to expand...
Click to expand...
Click to expand...
 
Please try my original suggestion:

Driver Pay:
Rate* (1 - [Admin Costs]/100) * ([driver percentage]/100)

and let me know what results you get.
--
Marsh
MVP [MS Access]


Driver Pay: [rate]*[driver percentage]/100
this is the expression I am currently using. I need to add in the admin
costs. I have "cut and paste" all the formulas you both have given me and it
still leaves out the admin costs. Instead of 23.75, I get 25.00.


Marshall Barton said:
How about that, my original guess was the right one ;-)

Now let's go back and try the expression I posted earlier
and see if we can figure out what about it, or your data,
leads you to think "it doesn't work".

Do you have a data record with a Rate of 100, Admin of 5 and
Dirver 25? Try it and see what result you get, it should be
23.75. If not, pleas post back with the result you did get
along with a Copy/Paste of the **exact** expression you're
using.

Check Andreas posts too. We are both suggesting equivalent
expressions that will produce the same results. He also
makes a good point about using names with spaces or other
funky characters.
Click to expand...
Click to expand...
 
I did try your original suggestion and I stil get 25.00 not 23.75

Marshall Barton said:
Please try my original suggestion:

Driver Pay:
Rate* (1 - [Admin Costs]/100) * ([driver percentage]/100)

and let me know what results you get.
--
Marsh
MVP [MS Access]


Driver Pay: [rate]*[driver percentage]/100
this is the expression I am currently using. I need to add in the admin
costs. I have "cut and paste" all the formulas you both have given me and it
still leaves out the admin costs. Instead of 23.75, I get 25.00.


Marshall Barton said:
How about that, my original guess was the right one ;-)

Now let's go back and try the expression I posted earlier
and see if we can figure out what about it, or your data,
leads you to think "it doesn't work".

Do you have a data record with a Rate of 100, Admin of 5 and
Dirver 25? Try it and see what result you get, it should be
23.75. If not, pleas post back with the result you did get
along with a Copy/Paste of the **exact** expression you're
using.

Check Andreas posts too. We are both suggesting equivalent
expressions that will produce the same results. He also
makes a good point about using names with spaces or other
funky characters.
Click to expand...
Click to expand...
Click to expand...
 
It gives me the right answer. Are you sure Admin Costs is
spelled correctly or has a value of 5?

I doubt that this is the problem, but you should be aware
the Rate is the name of a built-in function.
--
Marsh
MVP [MS Access]

I did try your original suggestion and I stil get 25.00 not 23.75

Marshall Barton said:
Please try my original suggestion:

Driver Pay:
Rate* (1 - [Admin Costs]/100) * ([driver percentage]/100)

and let me know what results you get.

Driver Pay: [rate]*[driver percentage]/100
this is the expression I am currently using. I need to add in the admin
costs. I have "cut and paste" all the formulas you both have given me and it
still leaves out the admin costs. Instead of 23.75, I get 25.00.


:
How about that, my original guess was the right one ;-)

Now let's go back and try the expression I posted earlier
and see if we can figure out what about it, or your data,
leads you to think "it doesn't work".

Do you have a data record with a Rate of 100, Admin of 5 and
Dirver 25? Try it and see what result you get, it should be
23.75. If not, pleas post back with the result you did get
along with a Copy/Paste of the **exact** expression you're
using.

Check Andreas posts too. We are both suggesting equivalent
expressions that will produce the same results. He also
makes a good point about using names with spaces or other
funky characters.
Click to expand...
Click to expand...
Click to expand...
 
i did a cut and paste and took your formula and pasted in the query under
design mode. I still comeup with the same 25.00

Marshall Barton said:
It gives me the right answer. Are you sure Admin Costs is
spelled correctly or has a value of 5?

I doubt that this is the problem, but you should be aware
the Rate is the name of a built-in function.
--
Marsh
MVP [MS Access]

I did try your original suggestion and I stil get 25.00 not 23.75

Marshall Barton said:
Please try my original suggestion:

Driver Pay:
Rate* (1 - [Admin Costs]/100) * ([driver percentage]/100)

and let me know what results you get.


dasad wrote:
Driver Pay: [rate]*[driver percentage]/100
this is the expression I am currently using. I need to add in the admin
costs. I have "cut and paste" all the formulas you both have given me and it
still leaves out the admin costs. Instead of 23.75, I get 25.00.


:
How about that, my original guess was the right one ;-)

Now let's go back and try the expression I posted earlier
and see if we can figure out what about it, or your data,
leads you to think "it doesn't work".

Do you have a data record with a Rate of 100, Admin of 5 and
Dirver 25? Try it and see what result you get, it should be
23.75. If not, pleas post back with the result you did get
along with a Copy/Paste of the **exact** expression you're
using.

Check Andreas posts too. We are both suggesting equivalent
expressions that will produce the same results. He also
makes a good point about using names with spaces or other
funky characters.
Click to expand...
Click to expand...
Click to expand...
 
- Create a new query in design view
- Add the table as your datasource
- Create the following calculated columns:
DriversRate: Format([Rate],"#,##0.00")
AdminPercent: Format([Admin Costs],"#,##0.00")
DriverPercent: Format([Driver Percentage],"#,##0.00")
- Run the query and check the result of your output
- For your example, you should now have the result:
100 5 25
If not, then the problem is with the data.

Regards,
Andreas

i did a cut and paste and took your formula and pasted in the query under
design mode. I still comeup with the same 25.00

:

It gives me the right answer. Are you sure Admin Costs is
spelled correctly or has a value of 5?

I doubt that this is the problem, but you should be aware
the Rate is the name of a built-in function.
--
Marsh
MVP [MS Access]

I did try your original suggestion and I stil get 25.00 not 23.75

:

Please try my original suggestion:

Driver Pay:
Rate* (1 - [Admin Costs]/100) * ([driver percentage]/100)

and let me know what results you get.


dasad wrote:

Driver Pay: [rate]*[driver percentage]/100
this is the expression I am currently using. I need to add in the admin
costs. I have "cut and paste" all the formulas you both have given me and it
still leaves out the admin costs. Instead of 23.75, I get 25.00.


:

How about that, my original guess was the right one ;-)

Now let's go back and try the expression I posted earlier
and see if we can figure out what about it, or your data,
leads you to think "it doesn't work".

Do you have a data record with a Rate of 100, Admin of 5 and
Dirver 25? Try it and see what result you get, it should be
23.75. If not, pleas post back with the result you did get
along with a Copy/Paste of the **exact** expression you're
using.

Check Andreas posts too. We are both suggesting equivalent
expressions that will produce the same results. He also
makes a good point about using names with spaces or other
funky characters.
Click to expand...
Click to expand...
Click to expand...
 
I did get the correct results from the query you told me to run. SO the data
is not the problem. Do I need to rename my "rate " column? would that help?

Andreas said:
- Create a new query in design view
- Add the table as your datasource
- Create the following calculated columns:
DriversRate: Format([Rate],"#,##0.00")
AdminPercent: Format([Admin Costs],"#,##0.00")
DriverPercent: Format([Driver Percentage],"#,##0.00")
- Run the query and check the result of your output
- For your example, you should now have the result:
100 5 25
If not, then the problem is with the data.

Regards,
Andreas

i did a cut and paste and took your formula and pasted in the query under
design mode. I still comeup with the same 25.00

:

It gives me the right answer. Are you sure Admin Costs is
spelled correctly or has a value of 5?

I doubt that this is the problem, but you should be aware
the Rate is the name of a built-in function.
--
Marsh
MVP [MS Access]


dasad wrote:

I did try your original suggestion and I stil get 25.00 not 23.75

:

Please try my original suggestion:

Driver Pay:
Rate* (1 - [Admin Costs]/100) * ([driver percentage]/100)

and let me know what results you get.


dasad wrote:

Driver Pay: [rate]*[driver percentage]/100
this is the expression I am currently using. I need to add in the admin
costs. I have "cut and paste" all the formulas you both have given me and it
still leaves out the admin costs. Instead of 23.75, I get 25.00.


:

How about that, my original guess was the right one ;-)

Now let's go back and try the expression I posted earlier
and see if we can figure out what about it, or your data,
leads you to think "it doesn't work".

Do you have a data record with a Rate of 100, Admin of 5 and
Dirver 25? Try it and see what result you get, it should be
23.75. If not, pleas post back with the result you did get
along with a Copy/Paste of the **exact** expression you're
using.

Check Andreas posts too. We are both suggesting equivalent
expressions that will produce the same results. He also
makes a good point about using names with spaces or other
funky characters.
Click to expand...
Click to expand...
Click to expand...
 
Are you sure that you got:
DriversRate: 100.00
AdminPercent: 5.00
DriverPercent: 25.00

The only explanation I can think of for the results you're
seeing is if AdminPercent were a very small number such as 0
or 0.05. Are you sure the 5 for AdminPercent is really an
integer 5 and not 5% or ???

If you are really getting **exactly** the numbers above,
then Paste my expression into another field in your test
query and double check the results against the data.
--
Marsh
MVP [MS Access]

I did get the correct results from the query you told me to run. SO the data
is not the problem. Do I need to rename my "rate " column? would that help?

Andreas said:
- Create a new query in design view
- Add the table as your datasource
- Create the following calculated columns:
DriversRate: Format([Rate],"#,##0.00")
AdminPercent: Format([Admin Costs],"#,##0.00")
DriverPercent: Format([Driver Percentage],"#,##0.00")
- Run the query and check the result of your output
- For your example, you should now have the result:
100 5 25
If not, then the problem is with the data.

i did a cut and paste and took your formula and pasted in the query under
design mode. I still comeup with the same 25.00

:
It gives me the right answer. Are you sure Admin Costs is
spelled correctly or has a value of 5?

I doubt that this is the problem, but you should be aware
the Rate is the name of a built-in function.


dasad wrote:
I did try your original suggestion and I stil get 25.00 not 23.75

:
Please try my original suggestion:

Driver Pay:
Rate* (1 - [Admin Costs]/100) * ([driver percentage]/100)

and let me know what results you get.


dasad wrote:
Driver Pay: [rate]*[driver percentage]/100
this is the expression I am currently using. I need to add in the admin
costs. I have "cut and paste" all the formulas you both have given me and it
still leaves out the admin costs. Instead of 23.75, I get 25.00.


:
How about that, my original guess was the right one ;-)

Now let's go back and try the expression I posted earlier
and see if we can figure out what about it, or your data,
leads you to think "it doesn't work".

Do you have a data record with a Rate of 100, Admin of 5 and
Dirver 25? Try it and see what result you get, it should be
23.75. If not, pleas post back with the result you did get
along with a Copy/Paste of the **exact** expression you're
using.

Check Andreas posts too. We are both suggesting equivalent
expressions that will produce the same results. He also
makes a good point about using names with spaces or other
funky characters.
Click to expand...
Click to expand...
Click to expand...
 
Now add the following columns to the query:
AdminRate: DriversRate * AdminPercent / 100
DriversPayment: (DriversRate - AdminRate) * DriverPercent / 100

Regards,
Andreas

I did get the correct results from the query you told me to run. SO the data
is not the problem. Do I need to rename my "rate " column? would that help?

:

- Create a new query in design view
- Add the table as your datasource
- Create the following calculated columns:
DriversRate: Format([Rate],"#,##0.00")
AdminPercent: Format([Admin Costs],"#,##0.00")
DriverPercent: Format([Driver Percentage],"#,##0.00")
- Run the query and check the result of your output
- For your example, you should now have the result:
100 5 25
If not, then the problem is with the data.

Regards,
Andreas

i did a cut and paste and took your formula and pasted in the query under
design mode. I still comeup with the same 25.00

:



It gives me the right answer. Are you sure Admin Costs is
spelled correctly or has a value of 5?

I doubt that this is the problem, but you should be aware
the Rate is the name of a built-in function.
--
Marsh
MVP [MS Access]


dasad wrote:


I did try your original suggestion and I stil get 25.00 not 23.75

:


Please try my original suggestion:

Driver Pay:
Rate* (1 - [Admin Costs]/100) * ([driver percentage]/100)

and let me know what results you get.


dasad wrote:


Driver Pay: [rate]*[driver percentage]/100
this is the expression I am currently using. I need to add in the admin
costs. I have "cut and paste" all the formulas you both have given me and it
still leaves out the admin costs. Instead of 23.75, I get 25.00.


:


How about that, my original guess was the right one ;-)

Now let's go back and try the expression I posted earlier
and see if we can figure out what about it, or your data,
leads you to think "it doesn't work".

Do you have a data record with a Rate of 100, Admin of 5 and
Dirver 25? Try it and see what result you get, it should be
23.75. If not, pleas post back with the result you did get
along with a Copy/Paste of the **exact** expression you're
using.

Check Andreas posts too. We are both suggesting equivalent
expressions that will produce the same results. He also
makes a good point about using names with spaces or other
funky characters.
Click to expand...
Click to expand...
Click to expand...
 
I just finished jumping up and down and shouting for joy. IT WORKS! IT
WORKS! IT WORKS!.

Thank you so much for all your time and effort. You don't know how much I
appreciate it. I had just got to the point where I was going to give and try
and figure a long way of doing it. Thank you Thank you thank you.
Andreas said:
Now add the following columns to the query:
AdminRate: DriversRate * AdminPercent / 100
DriversPayment: (DriversRate - AdminRate) * DriverPercent / 100

Regards,
Andreas

I did get the correct results from the query you told me to run. SO the data
is not the problem. Do I need to rename my "rate " column? would that help?

:

- Create a new query in design view
- Add the table as your datasource
- Create the following calculated columns:
DriversRate: Format([Rate],"#,##0.00")
AdminPercent: Format([Admin Costs],"#,##0.00")
DriverPercent: Format([Driver Percentage],"#,##0.00")
- Run the query and check the result of your output
- For your example, you should now have the result:
100 5 25
If not, then the problem is with the data.

Regards,
Andreas


dasad wrote:

i did a cut and paste and took your formula and pasted in the query under
design mode. I still comeup with the same 25.00

:



It gives me the right answer. Are you sure Admin Costs is
spelled correctly or has a value of 5?

I doubt that this is the problem, but you should be aware
the Rate is the name of a built-in function.
--
Marsh
MVP [MS Access]


dasad wrote:


I did try your original suggestion and I stil get 25.00 not 23.75

:


Please try my original suggestion:

Driver Pay:
Rate* (1 - [Admin Costs]/100) * ([driver percentage]/100)

and let me know what results you get.


dasad wrote:


Driver Pay: [rate]*[driver percentage]/100
this is the expression I am currently using. I need to add in the admin
costs. I have "cut and paste" all the formulas you both have given me and it
still leaves out the admin costs. Instead of 23.75, I get 25.00.


:


How about that, my original guess was the right one ;-)

Now let's go back and try the expression I posted earlier
and see if we can figure out what about it, or your data,
leads you to think "it doesn't work".

Do you have a data record with a Rate of 100, Admin of 5 and
Dirver 25? Try it and see what result you get, it should be
23.75. If not, pleas post back with the result you did get
along with a Copy/Paste of the **exact** expression you're
using.

Check Andreas posts too. We are both suggesting equivalent
expressions that will produce the same results. He also
makes a good point about using names with spaces or other
funky characters.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top