conditional math

[Guest]

Below is a previous post I did and beyond the second reply I haven't head
anything else on it. I have since changed the master table so that the check
boxes are no longer in the table, they're now an interger reading either "0"
or "-1". Now the master table looks basicly like this:


Pilot ID First Name Last Name Admiistrative Specialist Medic
1 John Doe -1 0
0
2 Sally Smith 0 -1
-1

and so on

the table the those coulms reference to is the Admin Salary table:

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

The equasion i need is this:

Modified Salary + 1/2 of true arguments


now what I was thinking the string should look like is something like this:

sub AdminMod (IIf

![Mechwarrior Table]![Administrative]=true
then [query]![Mechwarrior Salary]![Salary]+

![Admin Salary]![Yearly
Salary]/2

end sub


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

:

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


:

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

 

[Guest]

I would not use an un-normalized table structure like this that requires
calculations across fields rather than across records. Have you considered
making Administrative, Specialis, and Medic field VALUES rather than field
NAMES?

If you can't or won't change you structure, my next suggestion would be to
create a normalized structure with a union query.
SELECT PilotID, 1 as AdminID
FROM table
WHERE Administrative = -1
UNION ALL
SELECT PilotID, 2
FROM table
WHERE Specialist = -1
UNION ALL
SELECT PilotID, 3
FROM table
WHERE Medic = -1;

You can then calculate across records.

--
Duane Hookom
Microsoft Access MVP

Below is a previous post I did and beyond the second reply I haven't head
anything else on it. I have since changed the master table so that the check
boxes are no longer in the table, they're now an interger reading either "0"
or "-1". Now the master table looks basicly like this:


Pilot ID First Name Last Name Admiistrative Specialist Medic
1 John Doe -1 0
0
2 Sally Smith 0 -1
-1

and so on

the table the those coulms reference to is the Admin Salary table:

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

The equasion i need is this:

Modified Salary + 1/2 of true arguments


now what I was thinking the string should look like is something like this:

sub AdminMod (IIf

![Mechwarrior Table]![Administrative]=true
then [query]![Mechwarrior Salary]![Salary]+

![Admin Salary]![Yearly
Salary]/2

end sub


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

:

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


:

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

 

[Guest]

Duane,

I thought about using them as values, but the problem is, is that more than
one can apply, and as far as I know Access(wich isn't all that much) you
can't have more than one value in a field. I mean if there's a better way to
do this, I'm all "ears".

I tried to put the syntax that you wrote into my query and it told me "Check
the subquery's syntax and enclose the subquery in parentheres." Am I putting
it in in the wrong place or something? I'm trying to teach myself access and
vba and it's not working too well.

I would not use an un-normalized table structure like this that requires
calculations across fields rather than across records. Have you considered
making Administrative, Specialis, and Medic field VALUES rather than field
NAMES?

If you can't or won't change you structure, my next suggestion would be to
create a normalized structure with a union query.
SELECT PilotID, 1 as AdminID
FROM table
WHERE Administrative = -1
UNION ALL
SELECT PilotID, 2
FROM table
WHERE Specialist = -1
UNION ALL
SELECT PilotID, 3
FROM table
WHERE Medic = -1;

You can then calculate across records.

--
Duane Hookom
Microsoft Access MVP

Below is a previous post I did and beyond the second reply I haven't head
anything else on it. I have since changed the master table so that the check
boxes are no longer in the table, they're now an interger reading either "0"
or "-1". Now the master table looks basicly like this:


Pilot ID First Name Last Name Admiistrative Specialist Medic
1 John Doe -1 0
0
2 Sally Smith 0 -1
-1

and so on

the table the those coulms reference to is the Admin Salary table:

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

The equasion i need is this:

Modified Salary + 1/2 of true arguments


now what I was thinking the string should look like is something like this:

sub AdminMod (IIf

![Mechwarrior Table]![Administrative]=true
then [query]![Mechwarrior Salary]![Salary]+

![Admin Salary]![Yearly
Salary]/2

end sub


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

:

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


:

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

 

[Guest]

You would need to create a new table with the PilotID (to link to your
current table) and an AdminID field. If a pilot has Adminstrative and
Specialist, this would add two records to the new table. To get an idea of
what this new table will look like, just create a new query with SQL similar
to the union query I suggested.

I didn't want you to add the union query directly into your query. Create
and save the union query. You can then combine the union query with other
tables/queries to calculate your values.
--
Duane Hookom
Microsoft Access MVP

Duane,

I thought about using them as values, but the problem is, is that more than
one can apply, and as far as I know Access(wich isn't all that much) you
can't have more than one value in a field. I mean if there's a better way to
do this, I'm all "ears".

I tried to put the syntax that you wrote into my query and it told me "Check
the subquery's syntax and enclose the subquery in parentheres." Am I putting
it in in the wrong place or something? I'm trying to teach myself access and
vba and it's not working too well.

I would not use an un-normalized table structure like this that requires
calculations across fields rather than across records. Have you considered
making Administrative, Specialis, and Medic field VALUES rather than field
NAMES?

If you can't or won't change you structure, my next suggestion would be to
create a normalized structure with a union query.
SELECT PilotID, 1 as AdminID
FROM table
WHERE Administrative = -1
UNION ALL
SELECT PilotID, 2
FROM table
WHERE Specialist = -1
UNION ALL
SELECT PilotID, 3
FROM table
WHERE Medic = -1;

You can then calculate across records.

--
Duane Hookom
Microsoft Access MVP

Below is a previous post I did and beyond the second reply I haven't head
anything else on it. I have since changed the master table so that the check
boxes are no longer in the table, they're now an interger reading either "0"
or "-1". Now the master table looks basicly like this:


Pilot ID First Name Last Name Admiistrative Specialist Medic
1 John Doe -1 0
0
2 Sally Smith 0 -1
-1

and so on

the table the those coulms reference to is the Admin Salary table:

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

The equasion i need is this:

Modified Salary + 1/2 of true arguments


now what I was thinking the string should look like is something like this:

sub AdminMod (IIf

![Mechwarrior Table]![Administrative]=true
then [query]![Mechwarrior Salary]![Salary]+

![Admin Salary]![Yearly
Salary]/2

end sub


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

:

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


:

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