Need Help with IIF and Report

[gjmac2]

I am trying to duplicate an extremly complex report from an older db in a new acess db.

Here is my problem:

The query fields I am using are entitled "Region", "Heart UNOS Code" and "Heart Recovery". I am trying to retrieve sums of the "Heart Recovery" field based on distinct values in the "Heart UNOS Code" field.

For example, two of the codes are 501 and 504. On my report, I would like to retrive distinct sums based on these codes while ignoring the other codes. So, ideally my report would look something like:

code 501 13

code 502 7


I have tried and tried and cannot seem to come up with the answer I need. Please help.

 

[dhookom]

You should be able to create a totals/group by query that groups by [Heart UNOS Code] and sums [Heart Recovery].

SELECT [Heart UNOS Code], sum([Heart Recovery]) as SumHR
FROM [No Name Given]
GROUP BY [Heart UNOS Code];

USe this query as the record source of your report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[gjmac2]

Thank you for your reply.

Please forgive my ignorance, but where would I create the totals/group by query that you mentioned?

Thank you again.

 

[dhookom]

It's a query. A report has a Record Source property which is usually a table, query, or SQL statement.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]