Advice Charity - Report Design

[CutAndPaste]

I have a table that lists various information about the Clients at a Charity Advice Service. I'm aiming to produce (amongst others) a report that details the breakdown of Clients by male and female in particular categories in a chosen field (for example as in Employment below). My Report needs to be in the format as shown


Table 6 : Employment
_________________________________________________________________

Employment status Females Males Total
_________________________________________________________________
Unemployed 323 32% 704 55% 1027 45%
Employed 138 14% 97 8% 235 10%
Self-employed 6 1% 13 1% 19 1%
On Govt. scheme 6 1% 8 1% 14 1%
Full time at home 163 16% 16 1% 179 8%
Carer 44 4% 11 1% 55 2%
Student 33 3% 17 1% 50 2%
Retired 184 18% 187 15% 371 16%
Long Term sick 118 12% 232 18% 350 15%
_________________________________________________________________

Totals -> 1015 44% 1285 56% 2300
_________________________________________________________________

My first port of call for this was by doing a cross tab query and then working out the percentages on a report but I just can't seem to get it to work. Should I calculate the various percentages at query or report level? How would I do this?

Any Ideas?

 

[Carol]

Continue with the idea of using a Crosstab Query for this report with your Status Field being your Row Heading and your Sex Field being your Column Heading and your counts being your Value.

On your report, create the additional calculation fields to hold the sub-totals, totals and percentages.

 

[CutAndPaste]

Carol,

Tried this and it didn't work until I actually re-read your post, then it worked first time. As I'm using look up tables for the main data I then pulled in the "English" Field Values and I got my report looking realyl good.

However, I can make this work from my main table but when I try to run it through a parameter query (dates selected on a form) I get a message saying that "The Microsoft Jet Database Engine does not recognise Forms![FormName]![StartDate] as a valid Field Name or Expression" Any further ideas?

Many thanks for your earlier reply.

Simon

 

[CutAndPaste]

When all else fails I find using the inbuilt help system invaluable...

Text Cut from Access Help File...

The specified name isn't a recognized field name or a valid expression. In a query, this error can occur if you enter a name that improperly refers to a database, table, or field.
Possible causes with Microsoft Access:

· You have a parameter in a crosstab query or in a query that a crosstab query or chart is based on, and the parameter data type isn't explicitly specified in the Query Parameters dialog box. To solve the problem:

· In the query that contains the parameter, specify the parameter and its data type in the Query Parameters dialog box. And;
· Set the ColumnHeadings property for the query that contains the parameter.

· In any type of query, you've improperly referred to a database, table, or field. For example, this error can occur if you refer to a field named Salary in an expression, but you misspell the field name, such as [Sallary]*1.1.

Simon

 

[CutAndPaste]

Not quite so easy as I thought...

Ok I've specified the Start and End Dates in the Query parameters and even got the sum of male and females calculated in the Cross Tab query. More importantly I can limit the output of the query by having the VisitDate field reference the start & end dates using a WHERE clause on the visit date field.

On my report bound to the query I can do basic summing of the total records for example [male] or [female] or ([male] + [female]) but NOT by using calculated controls on my report that refer to other calculated controls on my report - I have to refer back to fields in the parameter query. Similarly when I want to calculate percentages on the report it seems to work them out on the whole of the record set rather than just the parameter query selection.

This is driving me barmy. I can send a small sample Access 97 example for anyone with any further ideas.

 

[Carol]

You can send me a small sample and I will take a look at it.