Count Text Values on a report

hullstorage

Registered User.
Local time
Today, 13:03
Joined
Jul 18, 2007
Messages
213
I have a report that will show 3 different text values on a report as below

1st delivery is an 8ft

2nd delivery is an 8ft

3rd delivery is an 6ft

4th delivery is an 10ft

What i want is an expression of somesort to count 8ft
so this would look like this at the bottom of my report

8ft = 2
6ft = 1
10ft = 1

these are simply deliveries of certain size tables from my report
but there will be about 100 deliveries so we need to find out how many 8ft tables, 6ft tables and so on for loading order
 
If there are only the 3 you might do

=Sum(IIf(FieldName = "8ft", 1, 0))

If there possibilities are more dynamic, I'd use a subreport based on a totals query that would group by item and total up whatever items were present.
 
8ft = 2
6ft = 1
10ft = 1
Do you want to sum only 8ft or want to group by all categories
 
Did you try my solution?
 
Creat a sub report use groupby and count to achieve the above
Then insert that sub report at the footer of your main report
 
If there are only the 3 you might do

=Sum(IIf(FieldName = "8ft", 1, 0))

If there possibilities are more dynamic, I'd use a subreport based on a totals query that would group by item and total up whatever items were present.

thanks pbaldy

this worked a treat

simon
 
thanks pbaldy

this worked a treat

simon

hm. i'm trying to do the same, but i keep getting a data type mismatch error, or a prompt for a parameter value. (edit: i should mention that i'm attempting this in a REPORT.)

i want to count how many isolates i have of a particular colour. the colour field is called "Colour" and the control i've called "cboColour". [Colour] can have the value of "Pink" (ID=1) or "Blue" (ID=2).

i have tried variations on the theme. if i use anything except the field name it prompts me with what looks like a parameter dialog (but doesn't count correcly when i put in, say "pink" (without the quotes)....)

Code:
=Sum(IIf([Colour]="Pink","1","0"))
=Sum(IIf([Colour]="1","1","0"))
both bring up a data mismatch error

Code:
=Sum(IIf([cboColour]="Pink","1","0"))
=Sum(IIf([cboColour]="1","1","0"))
both prompt for the parameter for "cboColour". if i put in "Pink", it returns "0", if i put in "1" it returns a "150" (which is how many records there are in the query that the report is based off).

it's super frustrating especially considering how easy it is to do these counts/sums in excel!

i've also tried achieving this with VBA in the report header (where the count result is to be displayed) paint event (the rows of data are in the details section):

Code:
Private Sub ReportHeader_Paint()
    
    Dim lngPinkies As Long

    ' use the currently bound query
    Dim strRecordSource As String
    strRecordSource = Me.RecordSource

    'count those isolates which are pink
[COLOR=Red]    lngPinkies = DCount("[Colour]", strRecordSource, "[Colour] = '1'")
[/COLOR]    Me.txtCount_Pink.Value = lngPinkies

End Sub
with the above code i get a runtime error 3464: data type mismatch in criteria expression, and it highlights on the DCount line. (i am using strRecordSource = Me.RecordSource because i want this report as a 'template' for other queries with the same field names but different records - so i can't "hard-code" query names into the code)

i had also previously tried to implement the solution detailed in this thread here:
http://www.access-programmers.co.uk/forums/showthread.php?t=167489

but the results for this always shows as "#error". indeed, when i download bhammerstorm's db example, and change one of the colours in the one table from 'red' to 'blue' to see if that updates the counts in their report, it also returns "#error" in the count fields.

i also tried "flagging" when there is a pink isolate with an unbound text control in the detail section (and named it "fPink") whose source i made

Code:
=IIf([cboColour]="1","1","0")
which works to flag the pinkies, but then i haven't been able to 'count' or 'sum' this unbound field, which i read somewhere is impossible to do on unbound fields, but bhammer's example seems to somehow pull calculated report controls together... i just can't get my head around how!
 
Last edited:
Have you tried this:

=Sum(IIf([Colour]=1,1,0))
 
now - how would you count *unique* values? for example, i may have 20 isolates (17 blue and 3 pink - yay!), but what if they were isolated from only 6 patients - so my query has a patientID in each record, and if i do =Count([PatientID]) i get the same number as isolates b/c in the query there ARE that many patientIDs, but i want to know how many unique ones there are?
 
my advice is to use dsum(select statement) in a query.
 
Last edited by a moderator:
woooops, i did a big forum noob faux pas... i didn't give any specifics!

rommel: i can already achieve what i want by using several different queries with several different visible fields and 'unique values' set to "yes"..., and that's a quick solution, but this needs to be a little more user-friendly and 'summary' than playing with several queries (there are already several dozen queries in this DB, so for a novice user like my boss to find which one he needs and which isolates they relate to - sometimes even i have problems there! ;))... let me explain:

my setup at the moment: i have a query which pulls out certain isolates with various criteria. in my report, i have grouped by patient, then by sample, then in the detail are the isolates themselves (for those non microbiologists: a patients gives us a sample, and in it we can isolate various bugs, like e. coli, staph, etc etc... so one patient can give multiple samples, and one sample can give multiple isolates).

please see attachments for a visual clarification of what i'm about to explain
(
image one: design view of my report;
image two: report view, pointing to the counts in question;
pdf: whole report, see on the last page the running sums for patient and samples are 29 and 46, respectively - i want to 'capture' these
)

i have a list (query) which has all the fields in need. from this list, i load into a report. i want to be able to change the bound query at will, so i don't want to hard-code any querynames etc.

(all my queries that i want to apply to this report have the same fields: and no, i cannot union them, or anything b/c they are extremely complex queries that pull out various isolates depending on various criteria that we want to look at: like, either: all the isolates which grew on a Gentamicin plate; or those that subsequently grew on a mixed Timentin/Gent plate, but did not give a Gentamicin flag in the RLB result; etc...)

for reasons i won't go into now, we want to be able to count within these lists the following:

1) which isolates were pink, and which were blue
2) how many samples these isolates came from
3) how many patients these samples came from

in addition, these are to be 'grouped' into sample "criterias" we applied at the beginning of the study (e.g., criteria 1 = only those samples from patients that had been in ICU for longer than 3 days...; criteria 2: only those samples from patients >3 days in ICU AND received antibiotics... etc) and also 'grouped' by "cycle" (for simplicity, let's say that this is a study to see whether using one antibiotic is better than another - it's a little more convoluted than that, but bear with me.)

so, in the end we want something like this:

for isolates, which grew on Gentamicin plates:

IN ALL CRITERIA:
Patients: 29 (this i can't figure out how to put here)
Samples: 46 (this i can't figure out how to put here)
Pink isolates: 23 (this already works for all groupings)
Blue isolates: 127 (
(edit: sorry, i don't know why the forum 'reply' software keeps breaking into two quote boxes here, it's all supposed to be in one)
this already works for all groupings)
(total isolates: 150) (
this already works for all groupings)
IN CRITERIA 1
Patients: (to be either a direct count of unique ID_admissions, or a sum of all the cycle counts of patients in the criterion)
Samples:
Pinkies: 9
Blues: 53
(total): 62
IN CYCLE 1 OF CRITERIA 1
Patients: 3 (only available via the running sum, which i want to capture and place here in the 'summary' and be able to also combine all the cycle counts into a 'total' for criteria counts)
Samples: 5 (as above, except only 'groups' per patient, not per 'cycle')
Pinkies: 0
Blues: 10
(total): 10


IN CYCLE 2 OF CRITERIA 1
.
.
.
...IN CRITERIA 2
...IN CRITERIA 3....
.
.
.

...IN CRITERIA 5....
.
.
.

IN CYCLE 4 OF CRITERIA 5
.
.
.


now, you can see in my screen shot, i have circled certain things. - please note: "ID_admission" is patient, "BoxID" is sample, and the detail section has all the isolate details in it.

via the code that pbaldy helped with
=Sum(IIf([Colour]=1,1,0)) i am able to count how many pink isolates, and how many blue isolates there are in the currently bound recordset- and becuase i have 'grouped' my report by "ID_admission" then "BoxID", when i put a textbox into the header section of these groups, i can count the isolates with a certain colour there.

however, when i try to count how many patients (with =Count([ID_admission]) and "OverGroup" in the data properties) in either a criteria or cycle header, it actually counts all the RECORDS (b/c each record has an associated ID_admission and BoxID - the grouping seems not to 'clue-in' Access at all).

i have put into the ID_admission header a running sum (one texbox for "OverGroup and one for "OverAll") and this gets me the numbers i want for the Patients, BUT i can't seem to access that count in my critera/cycle header (where i have placed a summary of the other counts i can get, as you can (hopefully) see from my screenshot).

this, in effect, gives me a 'count' of 'unique' ID_admissions (i.e., patients) - and i've checked: the running sum "overall" and "over group" works in this instance for patients only: the running sum "over group" only gives me BoxIDs (i.e., samples) per patient as oppised to per cycle or per criteria, though the "over all" gives me the correct value over the whole list.

i've tried accessing that running sum by using another texbox with =Max([txtAdmit_ALL]), for example, but as i've encountered before, i cannot reference or make caluclations etc on an unbound textbox in a report.

... while the running sum thing gets me 90% there - i NEED that 'max' value (i.e., unique count of patients, and unique count of samples) in my designated 'summary' areas in the page header, and Criteria/Cycle headers. THIS IS BECAUSE it's easier to read in a summary, as opposed to finding the last row of each group etc. AND i want to give the user the oportunity to either print with the details (counts and isolate details), or without the details (i.e., just the counts)

now. does anyone have any ideas how i can access the running sum that access is obviously capable of, or other unique count method, that doesn't involved making more queries or subreports? (b/c like i said, the report will be used as a shell for various query results...) UNLESS a subreport can 'dynamically'(?) make counts on whatever the query source is for the main report...? if you know what i mean?... that's ok, so long as it's possible to have just the one subreport and for it to take on any changed query data in the main report too...
 

Attachments

  • Counting Values Design.jpg
    Counting Values Design.jpg
    91.1 KB · Views: 401
  • Counting Values Report.jpg
    Counting Values Report.jpg
    45 KB · Views: 365
  • rptGENTisolates.pdf
    rptGENTisolates.pdf
    51.5 KB · Views: 311
Last edited:
here is what the report will look like with the ID_admission_Header, BoxID_Header and Details Sections set to "Visible = No" in the property sheet.

(i changed the colours - they were a little horrid, weren't they) i haven't photoshopped this or anything - this is exactly what it looks like, and how i want it - save the lacking counts.

in this document, the admissions and samples counts either don't exits (b/c i haven't figured it out yet) or are wrong.

i wanted to have the admissions counted up, which is equivalent to a "unique" query, and same for samples BUT there has to be a way to do this via a report with existing bound query data? the running sum (in the detail section) is right ("OverAll"), but i can't access those counts somehow... (see my previous post).

btw, fyi: i want the user to chose whether or not they print the 'detail' as well b/c sometimes it is necessary to see whether certain isolates in one list are the same certain isolates in another list... but often we just are concerned with the summary counts...
 

Attachments

wikelndt

have you tried a totals query to get at these groupings/subgroupings you need

its easier than constructing sql directly
 
wikelndt

have you tried a totals query to get at these groupings/subgroupings you need

its easier than constructing sql directly

i haven't, but i'm having better luck with the report going the bhammerstrom way (though s/he credited allen browne for his/her success)- i delved into access help and after 30 min or so found 'dumb-speak' instructions on how to do (at least the beginning part) of what bhammer said to do in his/her last thread (though i only realised it was the same thing a little time later ;)). (regarding removing the lower-level groupings and using "hide duplicates" instead.) which has made the reports look better and are easier to read, too :)

i'm in the midst of actually nutting out how to get those counts into my textboxes, but have realised that i am actually getting somewhere when i use "print preview" (does the base counts/flags) rather than "report view" (shows "#error" in place of the counts/flags).

i have also distracted myself somewhat and added a 'details' hide feature, which you will remember from yesterday's IsLoaded thread ;)

i have also discovered that the counts don't work in the header section, and MUST be in the footer section of that group, while the =sum(iif()) textboxes don't work in the footer section but only the header!! (sheesh!) so what i've done is put my sum() textboxes in the header, made it invisible, and then have had the luck to be able to refer to that textbox in another unbound textbox... (i.e., refering the unbound sum control name =[txtPink_Count])

the totals query would work, i suppose, but could get complex when i change the Me.Recordsource depending in which query i want to feed the report 'shell'.

i'll keep that suggestion in mind, though, if i don't succeed in the bhammerstrom way.
 
and for anyone interested, here's what i've got so far... though not seemingly any progress on the actual counting bit, i do have hope!

all the long list of counting/running sum i will make invisible when i have success in using them to sum up my counts. :)
 

Attachments

ok! after fiddling a bit with the textbox running sum properties in the cycle group and report footers, i managed to achieve the counts i want in the patient (admission) and sample data! yay!

however, two thinks suck:

1) i can't seem to find a happy-medium for the 'criteria' group, which is an "in-between group" (my report is grouped first by criteria, then cycle, then has the detail section). the report seems to be to handle "over group" as if to mean the first level group (first one above detail, that is). so i can't seem to calculate how many patients/samples there are in each criteria, but i have the numbers for the cycles - this SHOULD just be an easy =Sum([txtSampleCycleCount]) but i run into that weird report feature of not being able to reference another unbound textbox... i even tried re-doing the 'calculation' from scratch, by-passing the txtSampleCycleCount box with =Sum(iif([txtBoxID].[Visible]=-1,1,0)), but it prompts for a parameter for [txtBoxID].[Visible].

2) if i make the count textbox controls invisible (on which my final counts depend) everything still works, HOWEVER if i make the whole detail SECTION invisible, then all the counts return "0" (zero)... :( (edit: except the colour counts - go figure!) which means i can't have just a nice neat summary report, i have to have all the details (i wanted the user to be able to toggle between the two).

here's my report output now - with the 2/3rds successful countup! only counting up by criteria is now still needed to be nutted out, and perhaps a detail.visible=no work-around, (though no essential, i can live with having all the detail - and prefer to be stuck WITH detail than NO detail)
 

Attachments

hi,

FYI

just one thing i'd like to mention: my reports have LOTS of isolates for any given sample for any given patient. i noticed with this method that my patient/sample controls repeated on a new page if the number of isolates took it over the page - visually this is not bad BUT it messed up the counts... it counted all the repeated controls as well as the 'real' non-duplicated controls...

to fix this, i changed the "keep together" property of the detail section (not the controls themselves) to "no"...
 

Users who are viewing this thread

Back
Top Bottom