checkbox conditional math

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

Guest

ok here goes,

I have a formula that I need a query to complete. The way I have my tables
set up is that the values are on refernce tables and the ID # is on the main
table, which from what I understand is the way it should be. Now there are
three check boxes on the main table. I can get the first half of the equasion
to work, where the ID#'s are used. What I can't get to work are the check box
values.

Now, the equasion I have so far is this:

Base salary * skill multiplier * Rank multiplier = modified salary

This is working just fine.

here's the trickey part:

IF the checkboxes are checked then I need it to add 1/2 of the corresponding
field.
I don't know how to associate the record on the table with the checkbox in
order to add it in to the equasion
 
Now there are three check boxes on the main table.
Proper phrasing check boxes are IN a table and are ON a form. Are they in a
table?
What are the names of your fields?

Try this substituting your checkbox name for Checkbox1, etc.

IIF([Checkbox1]=-1,[Base salary] *1.5, [Base salary]) *
IIF([Checkbox2]=-1,[skill multiplier] *1.5, [skill multiplier]) *
IIF([Checkbox3]=-1,[Rank multiplier] *1.5, [Rank multiplier]) = modified
salary
 
ok the checkboxes are in the master table. the three checkboxes are:

Admin, Specialist, and Medic,

now the first part of the equasion is working:

Base salary * skill multiplier * Rank multiplier = modified salary

now for the second part:

when a checkbox is checked it has to go to another table named Admin Salary,
the table looks like this:

Admin ID Admin Position Yearly Salary
1 Admin 12000
2 Specialist 14500
3 Medic 15000

then it takes the corresponding salary and adds 1/2 to the modified salary
from above

KARL DEWEY said:
Proper phrasing check boxes are IN a table and are ON a form. Are they in a
table?
What are the names of your fields?

Try this substituting your checkbox name for Checkbox1, etc.

IIF([Checkbox1]=-1,[Base salary] *1.5, [Base salary]) *
IIF([Checkbox2]=-1,[skill multiplier] *1.5, [skill multiplier]) *
IIF([Checkbox3]=-1,[Rank multiplier] *1.5, [Rank multiplier]) = modified
salary

--
KARL DEWEY
Build a little - Test a little


Smokeyhavoc said:
ok here goes,

I have a formula that I need a query to complete. The way I have my tables
set up is that the values are on refernce tables and the ID # is on the main
table, which from what I understand is the way it should be. Now there are
three check boxes on the main table. I can get the first half of the equasion
to work, where the ID#'s are used. What I can't get to work are the check box
values.

Now, the equasion I have so far is this:

Base salary * skill multiplier * Rank multiplier = modified salary

This is working just fine.

here's the trickey part:

IF the checkboxes are checked then I need it to add 1/2 of the corresponding
field.
I don't know how to associate the record on the table with the checkbox in
order to add it in to the equasion
Click to expand...
Click to expand...
 
You need to not be using checkboxes but a field to contain 'AdminID' then
you can left join the two tables in your query.

IIF([AdminID] Between 1 AND 3, ([Base salary] * [skill multiplier] * [Rank
multiplier]) + ([Yearly Salary]/2), ([Base salary] * [skill multiplier] *
[Rank multiplier]) = modified salary

--
KARL DEWEY
Build a little - Test a little


Smokeyhavoc said:
ok the checkboxes are in the master table. the three checkboxes are:

Admin, Specialist, and Medic,

now the first part of the equasion is working:

Base salary * skill multiplier * Rank multiplier = modified salary

now for the second part:

when a checkbox is checked it has to go to another table named Admin Salary,
the table looks like this:

Admin ID Admin Position Yearly Salary
1 Admin 12000
2 Specialist 14500
3 Medic 15000

then it takes the corresponding salary and adds 1/2 to the modified salary
from above

KARL DEWEY said:
Now there are three check boxes on the main table.
Click to expand...
Proper phrasing check boxes are IN a table and are ON a form. Are they in a
table?
What are the names of your fields?

Try this substituting your checkbox name for Checkbox1, etc.

IIF([Checkbox1]=-1,[Base salary] *1.5, [Base salary]) *
IIF([Checkbox2]=-1,[skill multiplier] *1.5, [skill multiplier]) *
IIF([Checkbox3]=-1,[Rank multiplier] *1.5, [Rank multiplier]) = modified
salary

--
KARL DEWEY
Build a little - Test a little


Smokeyhavoc said:
ok here goes,

I have a formula that I need a query to complete. The way I have my tables
set up is that the values are on refernce tables and the ID # is on the main
table, which from what I understand is the way it should be. Now there are
three check boxes on the main table. I can get the first half of the equasion
to work, where the ID#'s are used. What I can't get to work are the check box
values.

Now, the equasion I have so far is this:

Base salary * skill multiplier * Rank multiplier = modified salary

This is working just fine.

here's the trickey part:

IF the checkboxes are checked then I need it to add 1/2 of the corresponding
field.
I don't know how to associate the record on the table with the checkbox in
order to add it in to the equasion
Click to expand...
Click to expand...
Click to expand...
 
The problem with that is that more than one can aply, like Admin and
Specialist, or Medic and Specialist, or even all three. Which is why I chose
to use checkboxes.

KARL DEWEY said:
You need to not be using checkboxes but a field to contain 'AdminID' then
you can left join the two tables in your query.

IIF([AdminID] Between 1 AND 3, ([Base salary] * [skill multiplier] * [Rank
multiplier]) + ([Yearly Salary]/2), ([Base salary] * [skill multiplier] *
[Rank multiplier]) = modified salary

--
KARL DEWEY
Build a little - Test a little


Smokeyhavoc said:
ok the checkboxes are in the master table. the three checkboxes are:

Admin, Specialist, and Medic,

now the first part of the equasion is working:

Base salary * skill multiplier * Rank multiplier = modified salary

now for the second part:

when a checkbox is checked it has to go to another table named Admin Salary,
the table looks like this:

Admin ID Admin Position Yearly Salary
1 Admin 12000
2 Specialist 14500
3 Medic 15000

then it takes the corresponding salary and adds 1/2 to the modified salary
from above

KARL DEWEY said:
Now there are three check boxes on the main table.
Proper phrasing check boxes are IN a table and are ON a form. Are they in a
table?
What are the names of your fields?

Try this substituting your checkbox name for Checkbox1, etc.

IIF([Checkbox1]=-1,[Base salary] *1.5, [Base salary]) *
IIF([Checkbox2]=-1,[skill multiplier] *1.5, [skill multiplier]) *
IIF([Checkbox3]=-1,[Rank multiplier] *1.5, [Rank multiplier]) = modified
salary

--
KARL DEWEY
Build a little - Test a little


:

ok here goes,

I have a formula that I need a query to complete. The way I have my tables
set up is that the values are on refernce tables and the ID # is on the main
table, which from what I understand is the way it should be. Now there are
three check boxes on the main table. I can get the first half of the equasion
to work, where the ID#'s are used. What I can't get to work are the check box
values.

Now, the equasion I have so far is this:

Base salary * skill multiplier * Rank multiplier = modified salary

This is working just fine.

here's the trickey part:

IF the checkboxes are checked then I need it to add 1/2 of the corresponding
field.
I don't know how to associate the record on the table with the checkbox in
order to add it in to the equasion
Click to expand...
Click to expand...
Click to expand...
 
Sorry I don't mean to be a pain... But thanks for the help!

Smokeyhavoc said:
The problem with that is that more than one can aply, like Admin and
Specialist, or Medic and Specialist, or even all three. Which is why I chose
to use checkboxes.

KARL DEWEY said:
You need to not be using checkboxes but a field to contain 'AdminID' then
you can left join the two tables in your query.

IIF([AdminID] Between 1 AND 3, ([Base salary] * [skill multiplier] * [Rank
multiplier]) + ([Yearly Salary]/2), ([Base salary] * [skill multiplier] *
[Rank multiplier]) = modified salary

--
KARL DEWEY
Build a little - Test a little


Smokeyhavoc said:
ok the checkboxes are in the master table. the three checkboxes are:

Admin, Specialist, and Medic,

now the first part of the equasion is working:

Base salary * skill multiplier * Rank multiplier = modified salary

now for the second part:

when a checkbox is checked it has to go to another table named Admin Salary,
the table looks like this:

Admin ID Admin Position Yearly Salary
1 Admin 12000
2 Specialist 14500
3 Medic 15000

then it takes the corresponding salary and adds 1/2 to the modified salary
from above

:

Now there are three check boxes on the main table.
Proper phrasing check boxes are IN a table and are ON a form. Are they in a
table?
What are the names of your fields?

Try this substituting your checkbox name for Checkbox1, etc.

IIF([Checkbox1]=-1,[Base salary] *1.5, [Base salary]) *
IIF([Checkbox2]=-1,[skill multiplier] *1.5, [skill multiplier]) *
IIF([Checkbox3]=-1,[Rank multiplier] *1.5, [Rank multiplier]) = modified
salary

--
KARL DEWEY
Build a little - Test a little


:

ok here goes,

I have a formula that I need a query to complete. The way I have my tables
set up is that the values are on refernce tables and the ID # is on the main
table, which from what I understand is the way it should be. Now there are
three check boxes on the main table. I can get the first half of the equasion
to work, where the ID#'s are used. What I can't get to work are the check box
values.

Now, the equasion I have so far is this:

Base salary * skill multiplier * Rank multiplier = modified salary

This is working just fine.

here's the trickey part:

IF the checkboxes are checked then I need it to add 1/2 of the corresponding
field.
I don't know how to associate the record on the table with the checkbox in
order to add it in to the equasion
Click to expand...
Click to expand...
Click to expand...
 
Back
Top