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): 62IN 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...