Subtract from within the same column

[TJVFree]

Hello again,
I’m trying to find a way to get my query to subtract from within the same column but from two separate sets of data within the Gender grouping. Hopefully below helps explain what I’m trying to do

iif([Gender]=Male,([Mean with Ovg Pts]-[Mean with Ovg Pts]=Femail])

Name Gender P1 P2 P3
Corey Brank Female 24 16 13
Corey Brank Male 103 74.5 60.5
Corey Brank N/A 2 1.5 1

 

[TJVFree]

I’m sorry I’m trying to explain something I’m not sure how to do but I know what the end results should be

Gender Stat
Male 1
Female 2

So in my query I would ask that female stat 1 to subtract Male stat 2 = 1

 

[lameid]

What numbers or logic are you using to get those results? Is that the end result?

 

[TJVFree]

Yes, that is the end result

 

[lameid]

What numbers or logic are you using to get those results?

I just do not see how the source data gets you those results.

 

[dhookom]

I have to agree with lameid.

iif([Gender]=Male,([Mean with Ovg Pts]-[Mean with Ovg Pts]=Femail])

Please proof before posting.
Use []s when you are referencing a field name.
Use ""s when you are referencing a string value ex: "Male"
Correct spelling ex: Femail
Provide real table/query and field names.

How do you expect us to tie "[Mean with Ovg Pts]" with anything in your data or your results?

Use TGML to line up your data so it is easier to read. Isn't the following much easier to understand?
[tt]
Name Gender P1 P2 P3
============ ======= ===== ====== =======
Corey Brank Female 24.0 16.0 13.0
Corey Brank Male 103.0 74.5 60.5
Corey Brank N/A 2.0 1.5 1.0
[/tt]

Duane
Hook'D on Access
MS Access MVP

 

[TJVFree]

Thank you all for your help,
All the formulas were working great until I got to Race. Now it doesn’t seem like the Mean with Ovg Pts is subtracting with the -Mean with Ovg Pts.
Is there another way I should be doing this?

Thanks again for your help

AM_AN_Diff: Sum(IIf([Race]='White',[Mean with Ovg Pts],IIf([Race]='American Indian or Alaskan Native',-[Mean with Ovg Pts],0)))


AM_AN_Diff_Mean with Ung Pts: Sum(IIf([Race]='White',[Mean with Undg Pts],IIf([Race]='American Indian or Alaskan Native',-[Mean with Undg Pts],0)))


AM_AN_Diff_Incidence with Ovg Pts: Sum(IIf([Race]='White',[Incidence with Ovg Pts],IIf([Race]='American Indian or Alaskan Native',-[Incidence with Ovg Pts],0)))


AM_AN_Diff_Incidence with Undg Pts: Sum(IIf([Race]='White',[Incidence with Undg Pts],IIf([Race]='American Indian or Alaskan Native',-[Incidence with Undg Pts],0)))


Asian_PI_Diff_Mean with Ovg Pts: Sum(IIf([Race]='White',[Mean with Ovg Pts],IIf([Race]='Asian or Pacific Islander',-[Mean with Ovg Pts],0)))


Asian_PI_Diff_Mean with Ung Pts: Sum(IIf([Race]='White',[Mean with Undg Pts],IIf([Race]='Asian or Pacific Islander',-[Mean with Undg Pts],0)))


Asian_PI_Diff_Incidence with Ovg Pts: Sum(IIf([Race]='White',[Incidence with Ovg Pts],IIf([Race]='Asian or Pacific Islander',-[Incidence with Ovg Pts],0)))


Asian_PI_Diff_Incidence with Undg Pts: Sum(IIf([Race]='White',[Incidence with Undg Pts],IIf([Race]='Asian or Pacific Islander',-[Incidence with Undg Pts],0)))


Black_Diff_Mean with Ovg Pts: Sum(IIf([Race]='White',[Mean with Ovg Pts],IIf([Race]='Black',-[Mean with Ovg Pts],0)))


Black_Diff_Mean with Ung Pts: Sum(IIf([Race]='White',[Mean with Undg Pts],IIf([Race]='Black',-[Mean with Undg Pts],0)))


Black_Diff_Incidence with Ovg Pts: Sum(IIf([Race]='White',[Incidence with Ovg Pts],IIf([Race]='Black',-[Incidence with Ovg Pts],0)))


Black_Diff_Incidence with Undg Pts: Sum(IIf([Race]='White',[Incidence with Undg Pts],IIf([Race]='Black',-[Incidence with Undg Pts],0)))
[\code]

 

[lameid]

AM_AN_Diff: Sum(IIf([Race]='White',[Mean with Ovg Pts],IIf([Race]='American Indian or Alaskan Native', -[red]1 * [/red][Mean with Ovg Pts],0)))

I don't think you can subtract that way... I always have multipied by a negative one... I guess you could subtract from 0 as well.