By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,705 Members | 1,891 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,705 IT Pros & Developers. It's quick & easy.

Access Reports and IIF statements

P: 1

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????
Jan 8 '09 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 2.5K+
P: 2,545
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!

Jan 8 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.