Calculated SumIf fields in Access Reports/ Queries

[hammyish]

Morning all,

I was wondering if it is possible to have sumif (or equivalent) in access for report generation.

An example as to what I am trying to do is as follows:

I am tracking investors into projects, investors can invest into multiple projects so there is a many:many relationship so I have included an investor project table to provide 1:many links. I am currently generating a report which has each single investment made which also includes the project name and who is investing, this works all ok. I would like each row to include the sum of the amount invested in the project (as below). And following that, a percentage calculation to show the proportion that they invested in the respective project.

Investor	Project	Investing Amount	*Total Project Investment*	*% Hold*
A	Alpha	25	130	19%
B	Alpha	65	130	50%
B	Beta	20	60	33%
C	Beta	40	60	67%
E	Alpha	10	130	8%
F	Alpha	30	130	23%

Is this possible, your help is most appreciated.

Cheers,


Hammyish

[CJ_London]

You would sum in the section header or footer and do your percentage calculation there

not clear if you are wanting to group projects by investor or investor by project

[hammyish]

Thanks CJ_London. I am looking to group by investor which is why I am looking to have the total invested into a project and the investor's contribution in the report as another field, rather than a sum as it will not be grouped by a project. The dataset I am working on is more complex in terms of number of record and tables but hopefully I have explained myself ok.

Cheers,
Hammyish

[hammyish]

I have worked it out, joining the query with another query which does the summation for me. Thanks for your help!