Access Summary Report

[idareu]

Hi, I have a table with information such as age, town, sex etc. I wish to view a report something like this:

Sex
Male 30
Female 20

Age
1 to 4 10
5 to 10 10
11 to 19 17
20 to 29 22
30 to 39 23
over 39 30

The way I am doing it right now is adding a text box and using the dcount function for each parameter. A dcount for male, dcount for female, dcount for age 1 to 4 etc.. Is there an easier way to make a summary report like this?

[NeoPa]

Create a query that indicates, for each record, all the information you want to show :

1. [Sex].
2. [Age].

Create a report that has this information in the Detail section, but make the detail section hidden.

Create a Report Footer section that summarises the data as you need.

[Male] = "=Sum(IIf([Sex]='M',1,0))"
[Female] = "=Sum(IIf([Sex]='F',1,0))"
[AgeTo4] = "=Sum(IIf([Age]<5,1,0))"

etc.

[idareu]

Thanks a lot NeoPa! My summary looks great!

[NeoPa]

Good for you :-)
I'm glad that was able to point you in the right direction.

[zmbd]

query based solution

NeoPa's code certainly works; however, I've occasionally ran into issues with the "immediate-if" (IIF) function should it error in a report in that it will cause the entire report or form to fail to update.

Wish I could take credit for this; however, it’s an extension of the solution offered here at bytes.com:

http://bytes.com/topic/access/answers/903603-how-get-count-multiple-criteria

I've attached an example database... it's in msaccess2010,
just in case you can't open it:
We have the table "tbl_data" with [ID]-PK-Autonum, [PersonName]-text, [PersonSex]-text, [PersonAge]-numeric.long
I populated it with some 40ish records
[id]=1; [PersonName]= person_1; [PersonSex] = m; [PersonAge]=1
2;person_2;f;2
etc…….. for some 49 records with 10 female ("f") and 39 male ("m").

built the following query:

SELECT DISTINCT
 (SELECT Count(*) FROM tbl_data WHERE ([PersonSex]="m")) AS [Count_Male],
 (SELECT Count(*) FROM tbl_data WHERE ([PersonSex]="f")) AS [Count_Female],
 (SELECT Count(*) FROM tbl_data WHERE ([PersonAge]<5)) AS [Count_under5],
 (SELECT Count(*) FROM tbl_data WHERE ([PersonAge] BETWEEN 4 AND 11)) AS [Count_5to10],
 (SELECT Count(*) FROM tbl_data WHERE ([PersonAge] BETWEEN 10 AND 20)) AS [Count_11to19],
 (SELECT Count(*) FROM tbl_data WHERE ([PersonAge] BETWEEN 19 AND 30)) AS [Count_20to29],
 (SELECT Count(*) FROM tbl_data WHERE ([PersonAge] BETWEEN 29 AND 40)) AS [Count_30to39],
 (SELECT Count(*) FROM tbl_data WHERE ([PersonAge] >= 39)) AS [Count_over39]
FROM tbl_data;

You should then be able to use this in your report...

I have a very large data table I pull similar aggregate data from so instead of using the table directly, I build a query that pulls the last month’s data and then use something like the query above replacing the “tbl_data” with the “qry_lastm onth” etc... Might be easier to combine the two queries; however, I get lost in the SQL when try to do that :(

z

[NeoPa]

Z, your explanation of where you experienced problems with IIf() is less than basic. There are some places where it's not a good idea to use it, but short of explaining all of these I cannot respond directly to your comment (as it fundamentally says so little).

Your suggested solution, unlike the suggestions from the linked article, is an example of some very inefficient SQL. Your post is otherwise well formatted, and that's impressive in itself, but I couldn't leave your suggestion showing without explaining to the many readers that it is not a solution one could recommend. I'm not saying it couldn't work, but running so many subqueries to process fundamentally the same data over and over again is not good organisation of the issue I'm sorry to say. It would be the SQL equivalent of calling a whole bunch of Domain Aggregate (DCount() etc) function calls, which we warn against for exactly that reason. Your suggestion would certainly be more efficient, but only marginally so. The design would be similar (very clumsy I'm afraid), but as they're all together in a single query, optimisations would be made to increase performance automatically.

[zmbd]

Hmmm...

Unfortunately the only an alternative I had to offer... and as you said, only marginally better.

What I would love to see is a cross-tab query that solves this question... just don't see how to implement it for the question at hand.

As for the IIF():
- IFF() can have issues where there are no records to return
- if there are multiple fields using IFF() and a single one returns an unresolved result, then the remaining fields can fail to calculate.

Another source that points out these two issues:

http://allenbrowne.com/RecordCountError.html

There are a couple of other sites; however, they state basically the same thing.

Took me days to figure out what was happening to one of my reports until I ran across that webpage. First I had the issue where my report didn't have records to show, thus the counts should have been 0; instead, they showed #Error. Once I figured this out, then I miss wrote one of the IIF() which didn't resolve, causing the entire report to show #Error despite the fact the query was working. :(
It may be that we were using 2007 and they might have fixed it in 2010... who knows? not me.

[NeoPa]

The issues you raise are not a fault with IIf(). They are simply an illustration of how important it is to understand Nulls properly when designing databases (particularly).

Field values can be missing (Null) for various reasons in a query. Two that come to mind quickly, are :

1. The field never having been provided with data and having Null allowed as well as a dafault value.
2. The field being on the Many side of a 1:M outer join and the record linked to being non-existent.

Null may not be information in the format of the field itself, but it is far from lacking information (as it tells you there is no actual data for the field). IIf(), and various other functions as well, propagate Nulls in order to pass on that information (as too does the string + operator).

It is important for code and design to work with this understanding as it's important information in many cases, and in others, not coding / designing it to match the possible data will cause undesirable results. Typically, Nz() can be used in many situations to ensure non-propagation of Null values, but each situation should be handled dependent on the actual requirements of that situation.

zmbd:
Unfortunately the only an alternative I had to offer... and as you said, only marginally better.

Just to be clear, that was compared to a solution using all DCount() calls. It doesn't make it the best solution here I'm afraid.

[idareu]

I have another question. I would like to count the females of new cases separated from those of follow up cases. I do have a field named case type that tells me which records are new and which are follow up. Can
I add an & to the formula you gave me Neopa?

[idareu]

I was able to do the count adding another condition! Thanks!

[NeoPa]

The format of the Expression parameter (1st param of IIf()) is exactly as you'd expect to find within a SQL WHERE clause, so will support using AND, OR, and various other SQL supported keywords.