MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### conditional math

• Follow

```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:

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:

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.

"KARL DEWEY" wrote:

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" wrote:

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:
> >
> >     2             Specialist                14500
> >     3             Medic                     15000
> >
> > then it takes the corresponding salary and adds 1/2 to the modified salary
> > from above
> >
> > "KARL DEWEY" wrote:
> >
> > > >>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
> > >
> > >
> > > "Smokeyhavoc" wrote:
> > >
> > > > 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
> > > >

```
 0

```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.
FROM table
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

"Smokeyhavoc" wrote:

> 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:
>
>    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:
>
> 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.
>
> "KARL DEWEY" wrote:
>
>  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" wrote:
>
>  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:
> > >
> > >     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" wrote:
> > >
> > > > >>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
> > > >
> > > >
> > > > "Smokeyhavoc" wrote:
> > > >
> > > > > 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
> > > > >
>
```
 0

```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.

"Duane Hookom" wrote:

> 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
> 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
>
>
> "Smokeyhavoc" wrote:
>
> > 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:
> >
> >    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:
> >
> >   then [query]![Mechwarrior Salary]![Salary]+[table]![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.
> >
> > "KARL DEWEY" wrote:
> >
> >  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" wrote:
> >
> >  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:
> > > >
> > > >     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" wrote:
> > > >
> > > > > >>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
> > > > >
> > > > >
> > > > > "Smokeyhavoc" wrote:
> > > > >
> > > > > > 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
> > > > > >
> >
```
 0

```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
--
Duane Hookom
Microsoft Access MVP

"Smokeyhavoc" wrote:

> 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.
>
>
>
> "Duane Hookom" wrote:
>
> > 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
> >
> >
> > "Smokeyhavoc" wrote:
> >
> > > 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:
> > >
> > >    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:
> > >
> > >   then [query]![Mechwarrior Salary]![Salary]+[table]![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.
> > >
> > > "KARL DEWEY" wrote:
> > >
> > >  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" wrote:
> > >
> > >  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:
> > > > >
> > > > >     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" wrote:
> > > > >
> > > > > > >>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
> > > > > >
> > > > > >
> > > > > > "Smokeyhavoc" wrote:
> > > > > >
> > > > > > > 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
> > > > > > >
> > >
```
 0

3 Replies
102 Views

Similiar Articles:

7/26/2012 4:58:50 AM