How do I make a report with counts of more than one field?

[Leopardfist]

OK, this is an example table, and example of what I need in my report. If anyone can help me figure this out it would be great.

Very simple table: Employees

Employee ID - Number
Employee City - Text (Minneapolis or St. Paul)
Employee Sex - Text (Male or Female)
Employee Status - Text (Full Time or Part Time)

Now for my report I want it to basically show the following:

Employees in St. Paul: [count of how many employees live in St. Paul]
Employees in Minneapolis: [Count of how many in Minneapolis]
Total Employees: [Count of total employees]

Male Employees: [Count of Male Employees]
Female Employees: [Count of Females]

Full Time: [Count of full time employees]
Part Time: [Count of part time employees]



That basically shows how I need to gather counts on more than one field for a single report. I have beaten this problem previously by doing a crosstab for one value, and a subreport for the second value. Recently it became necessary to report the third value [count] and I tried adding a second subreport which did not work. After thinking I figured just changing to one single query to look for all values, I could make the report much easier and simpler. I tried adding the 4 fields to the query and choosing Count instead of group by, but when I run it it only shows the headings, no data/results.

Can someone help me or put me on the right track as to how to do this the easiest way?

P.S. If it would be easier, I could send you the actual database and show you which query I use now and what I need added to the report, if you think it would be easiest to see exactly what I have. But it is a large file, my main table has 40 or so fields.

 

[RoyVidar]

Where you need the count (group footer(s)/report footer?), add one text control per count you need. Then create the following controlsource (here I'm playing with the field "Employee City" and Minneapolis)

=ABS(Sum([Employee City] = "Minneapolis"))

 

[Leopardfist]

I tried this, but when I preview the report for the count value it shows #error.

I tried both of these statements.

=Abs(Sum([Meal]="REG"))

on that one, Meal is the name of the field in the table I have the report linked too.

=Abs(Sum([Special Housing Unit Inmate Data]![Meal]="REG"))
This one has the table name "Special Housing Unit Inmate Data" in it, plus the field name. Now when I run it, and get the error, and re-open the properties... the shown statement is changed.... it removes the [] from around the field name Meal.

I also tried doing it the way you say, using a query, but it had the same results.


Below is the actual table name, field names, and possible choices for each field. Maybe this will help you help me.

Table: Special Housing Unit Inmate Data
Field: A/D or D/S Data Type: Text Possible Entries: A/D D/S
Field: Meal Data Type: Text Possible Entries: C/F N/M N/P REG REL
Field: Cell Data Type: Text Possible Entries: SHU (A* thru D*) MED R&D


Now on the cell field, the field has the cell number of each inmate.... anywhere from A01-100 thru A01-112U, B02-100 Thru B02-112U, C03-113L thru C03-156L, D04-113L thru D04-156L.

On this count, what I want to count is how many total are on a cell starting with A, B, C or D. These are inmates housed in the Special Housing Unit. Then a total count of those with cell = MED and a count of those with cell = R&D.

On other reports, I separated each range by using Like"A*" or Like "B*". I am sure with this one I could use Like"A*" OR Like"B*" OR Like"C*" OR like"D*" to count all occupied cells.


Does this help to come up with a good statement?


My report will look something like this:


C/F: [count of number of inmates who are on C/F Meal status]
N/M: [count of number of inmates on N/M Meal status]
N/P: [Count of inmates on N/P meal Status]
REG: [count of inmates on REG meal status]
SHU TOTAL: [total of all meals]

A/D: [count of number of inmates on A/D status]
D/S: [count of number of inmates on D/S status

SHU: [count of number of inmates housed in A* thru D*]
MED: [count of number of inmates housed in MED]
R&D: [count of number of inmates housed in R&D]

All these fields are stored in the table named above.

 

[Leopardfist]

Also tried:

=Count([Special Housing Unit Inmate Data Table]!Meal="REG")

that did not work either, I still for value #error on the report.

 

[RoyVidar]

That is strange, I think. The syntax

=ABS(Sum([FieldName] = SomeValue))

or for text fields, also

=ABS(Sum([FieldName] Like "SomeValue*"))

should normally work. Couple of things to try

1 - where have you put the boxes (use one of the sections I mentioned)
2 - you are using the actual field names? Aggregates cannot be performed on controls, only the fields in the recordsource
3 - the names of fields from the query should be unique, if you have more fields with the same name, either remove one of them, or use alias (i e, in the field row of the query - MyNewName: TheFieldName)
4 - any field you use in a calculation, there should not be a control on the report having the same name, that might confuse Access. Say for a field called Meal, if you also have a control bound to it, the name of the control should not be Meal. Change it to txtMeal or something, so that it's differs from the name of the field it's bound to.

When you try this at first. Start with a working report, then try only one field.

If none of this makes any difference, I think we'd need to see what you're doing. If there's a possibility for it, import only relevant objects to a new database, so that we could recreate the challenge, strip any confidential information...

 

[Leopardfist]

How do I get the ampersand character into a label?

Thanks Roy, I finally got it to work. I sdont know what I was doing, but I just accidentally hit tab after editting it once, and then when I closed the properties box and checked the preview it worked. I guess maybe it was not saving the statement when I closed it with the cursor still in the data source field maybe? Well, it works now and I could not have done it without your help, thanks a bunch!

I am having one other problem trying to create my label to ID the control... I want to enter the text "R&D" but it alsways takes the & out and underlines the D.

I tried / and \ before it but it still disappears and places the link line under the next character. How do I tell Access that I want the literal character?

Text R&D = RD which I dont want.

 

[Leopardfist]

Hey Roy,

I got it to work like I said, but one aspect of it really surprised me.

As I said earlier, one part of my report was to show how many inmates are housed in SHU, which would take any inmate with a cell number starting with A, B, C or D. When I first got it to work and changed the statement to put all ranges in, I did it as below.

=Abs(Sum([Cell] Like "A*" or Like "B*" or Like "C*" or Like "D*"))

That did not work!

I had to do it like Math, shown below:

=Abs(Sum([Cell] Like "A*"))+Abs(Sum([Cell] Like "B*"))+Abs(Sum([Cell] Like "C*"))+Abs(Sum([Cell] Like "D*"))+Abs(Sum([Cell] Like "H*"))

I would of thought the OR statement would of worked... any idea why it didn't?

 

[RoyVidar]

Yes, you'd need to either repeat the field

=Abs(Sum([Cell] Like "A*" or [Cell] Like "B*" or [Cell] Like "C*" or [Cell] Like "D*"))

or try something like

=Abs(Sum([Cell] Like "[ABCD]*"))

You got the "escape" character for ampersands? Just double them up (R&&D)

 

[Leopardfist]

Man Roy,

I tried to use the exact same syntax on another report, and again I get errored out. This one is even simpler than the first. It is a report of a query, a select query, that lists all cells and who is in them. At the bottom I wanted a total of how many inmates are on the range, so I used =Abs(Sum([Last Name])) and it gives errors. Last Name is the name of the field in my query. I do it with last name, because on this query it lists ALL cells, that way we see who is in what cell, as well as see whioch cells are empty. So my cell count will always be the same, but my occupied cell count will change, so that is what I am trying to count.

This query and report are VERY small and simple... I could send them to you, but I am afraid that without the table you might not have all the info you need to troubleshoot it, plus it definitely wouldnt work without the table.

I really wish I knew what was keeping it from working last time, and what changed that suddenly let it work .

EDIT
OK Roy, I give up. I am going to create a back-up, empty almost all the records, and send it to you so you can tell me what my problem is, and I can learn from it. I will note exactly which report I am working on, and which query it uses (BOTH are very simple, you will probably see the error within a few seconds). How can I sent it to you? Or can I attach it here?

 

[RoyVidar]

The ABS(Sum([TheFieldName] <operator> <condition>)) thingie is a nice trick which does the following

It creates a boolean expression, i e [TheField] = 42 can be answered with either true or false. True and false are represented by -1 and 0 respectively, so by utilizing the aggregate Sum on that expression, one will sum all of the -1 and 0 in that field. Having 10 records, 5 of them true, the result is -5, then we utilize ABS, which makes it positive.

If you wan't to count all occurrences, just use the Count aggregate

=Count([TheFieldName])

 

[Leopardfist]

OK Bro,

In order to attach it as a file, I had to delete almost all of my queries, reports, extra tables and all data except for a single range, which is for the report you are workign on. That means it most likely will not run at all, but you will be able to see the table structure, my query design and my report design. If you need the other stuff, I can get it zipped to about 1.5MB, but this forum only allows like 300KB for an attachment.

 

[Leopardfist]

OH Yeah, I used Office XP Developer to make it!

 

[RoyVidar]

See again first reply "Where you need the count (group footer(s)/report footer?)", you've put it in the page footer, which doesn't work, here it seems the report footer is appropriate. Also, it seems you intend to count all records with information in the field Meal, just use =Count([Meal])

 

[Leopardfist]

Still errors out with: =Count([Meal])

I attached the DB minus just about everything except the table/query/report that I am doing, so you can look at it.

The report is named: SHU Cell Assignment (A-Range)

The control I want to do is the only one in the footer, you can't miss it!

PS: Just tested it, and even with all the stuff I deleted, you CAn open the report to see the error displayed. Hope this helps.

 

[RoyVidar]

Did You Try What I Suggested In My First Reply In This Thread? Stuff The Darn Thing Into To The Report Footer In Stead Of The Page Footer?

 

[Leopardfist]

Awesome! It worked! THANKS AGAIN BRO!

 

[Leopardfist]

Sorry bro, I had posted that before I read your last post. It worked perfectly once I placed it into the report footer. Sorry for the confusion, and again many thanks!

 

[RoyVidar]

you're welcome