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[table]![Mechwarrior Table]![Administrative]=true 
  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:
> > 
> > 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" 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
> > > >

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:
> 
> 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[table]![Mechwarrior Table]![Administrative]=true 
>   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:
> > > 
> > > 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" 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
> > > > >
> 

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:
> > 
> > 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[table]![Mechwarrior Table]![Administrative]=true 
> >   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:
> > > > 
> > > > 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" 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
> > > > > >
> > 

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


"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:
> > > 
> > > 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[table]![Mechwarrior Table]![Administrative]=true 
> > >   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:
> > > > > 
> > > > > 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" 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
> > > > > > >
> > >