Calculation problem in report

[pser]

Hi I have the following query:

SELECT [Selskap].[Navn], '19'+Mid([Person].[Personnr],5,2) AS År, IIf(((Mid([Person].[Personnr],9,1)) Mod 2)=1,"","1") AS Kvinne, IIf(((Mid([Person].[Personnr],9,1)) Mod 2)=0,"","1") AS Menn
FROM Selskap INNER JOIN (Avdeling INNER JOIN Person ON [Avdeling].[AvdID]=[Person].[AvdID]) ON [Selskap].[SelskapID]=[Avdeling].[SelskapID];

Which gives me data as example:

Name Year Female Male
Company1 1954 1
Company1 1960 1
Company1 1960 1
Company1 1970 1

Then in the report i would like to sum column Female and column Male. When i try to do this with =sum([Female]) i get the total sum for whole company1. I want to sum only female and another sum for only male for each company.

Any suggestion?

Steven

 

[Beerbrewer]

I do not quite understand your SQL or the results (not really straight) but a normal way of solving this is to add another group level: Gender for male/female