Access Reports and IIF statements

[cheater63]

Hi,

I am trying to provide a report for my organisation which is rather complex.
Basically the underlying query has two variable elements Variable A has 10 different values and Variable B has 6 values and I need a count of each occurence i.e. A1B1, A1B2 etc.
I have been advised to try inseting text boxes and placing a Count(Iif..... statement in them, but I am having problems trying to count where A = a value AND B = a value.

It's probably something simple! Any advice????

[Stewart Ross]

Hi. Main advice is not to do it that way!

Reports, like forms, should be based on queries wherever possible, as queries allow the use of calculated and other special-purpose fields. Access provides totals queries which group by the field combinations you include in the query.

Set up a totals query which counts the number of rows grouped by your two multi-valued fields, setting whatever criteria you need to select the correct rows. Base your report on this query, and your report will then simply be summarising existing counts from the underlying query on which the report is based. This is a much easier approach than trying to use unbound textboxes with bespoke counts.

If you are still not able to set up such a query then please post some details of the tables, field names and types involved (or indeed some sample data showing what it is you want to summarise). We will then be able to provide a skeleton of the SQL for you.

Welcome to Bytes!

-Stewart