count number of specific entries but over multiple fields!

[Guest]

I have a table that has a number of fields in which there could be entries
from 1-11. also each participant has multiple rows for data entry (which was
an unfortunate result of having to work around access limitations on number
of fields)

How can I count up all the ‘1’s’ (or any other specific entry) over multiple
fields and rows? I've included more detail below for anyone who's kind enough
to answer below in case its handy!


a summary of the two different ways I have tried to do this already (I
haven’t included all the multiple fields in which the ‘1’ could be located
though):

SELECT StudyNumber,
Count(Mon_Assessment1) AS CountOfMon_Assessment1, Count(Mon_Assessment2) AS
CountOfMon_Assessment2…. And so on with more fields
FROM InterventionRecord
GROUP BY StudyNumber
WHERE (((Mon_Assessment1)="1")) OR (((Mon_Assessment2)="1")) … and so on
with more fields

This version returns counts of ALL entries (not just ‘1’s’)

I’ve also tried putting the critieria ‘1’ directly into the ‘count’ function
of the query which comes out very similar in the coding:

SELECT StudyNumber
Count(Mon_Assessment1) AS CountOfMon_Assessment1, Count(Mon_Assessment2) AS
CountOfMon_Assessment2…. And so on with more fields
FROM InterventionRecord
GROUP BY InterventionRecord.StudyNumber
HAVING (((Count(Mon_Intervention1))=1)) OR (((Count(Mon_Intervention2))=1))
… and so on with more fields

But this doesn’t return anything. I basically just want it to look at each
field in turn and count up the number of a specific entry i.e. 1, within that
field to give me the total number of ‘1’s in that field grouped by the study
number (primary key) to overcome the fact that i have more than one row per
patient. i’m thinking I may need to use IIf functions so that it looks at
each field individually and sums /counts if the entry is _ but I’ve no idea
how to go about writing this code. can anyone help please?!

 

[Steve]

The root cause of your problem is that your tables are not designed
correctly. Table InterventionRecord should not have Assessment1,
Assessment2, etc. If you provide the fields in your patient table,
interventionrecord table and any other related assessment tables, we will
provide a recommended design of your tables.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[John Spencer]

OUCH! Your table design is wrong for a relational database, but if it is
too late to fix it you can get the counts you want by modifying the query.

SELECT StudyNumber
, Count(IIF(Mon_Assessment1 = 1, 1, Null) )as CountAssessment1
, Count(IIF(Mon_Assessment2 = 1, 1, Null) )as CountAssessment2
FROM InterventionRecord
GROUP BY StudyNumber

An alternative is to use
SELECT StudyNumber
, Abs(Sum(Mon_Assessment1 = 1 )) as CountAssessment1
, Abs(Sum(Mon_Assessment2 = 1 )) as CountAssessment2
FROM InterventionRecord
GROUP BY StudyNumber

Your table structure should have been more like
TableStudy
StudyID
StudyType
StudyNumber

TableAssessments
AssessmentID
AssessmentName
.... Other info relevant to the Assessment types

TableStudyAssessments
TSID (autonumber field)
StudyID
AssessmentID
AssessmentResult

Then it would be really easy to get the results for every response to every
assessement for every study in one query which could be filtered by various
where clauses.

SELECT StudyID
, AssessmentID
, AssessmentResult
, Count(AssessmentResults) as ResultCount
FROM tableStudyAssessments
GROUP BY StudyID, assessmentID, AssessmentResult


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Thanks for answering so quickly. the reason why there are multiple fields is
due to the nature of the intervention. there can be up to 4 different
assessment on any given day of the week so there is mon_assess1,
mon_assess2.... tues_assess1, tues_assess2 etc. and at the end of
involvement, i want to be able to look across all the relevant fields and
tally up the various number of assessment codes inputted. is this just not
possible with the design? i can explain more about the nature of the design
if you require?
cheers, emma

 

[Steve]

Yes, give more details and post the tables and the fields in each table.
I'll then give you a recommendation for the design of your tables.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Guest]

Hi John,

apologies for the delay - i've been on hols! thanks for your help.. i
realise the table design may be horrible but i'm hoping that an expression
like the one you've given below might be a way around it as the database
design can't really be changed now! I've tried both your suggestions in
various ways as below:

Both:

SELECT InterventionRecord.StudyNumber,
Count(IIf(Mon_Intervention1=1,1,0)) AS CountMon_Intervention1,
Count(IIf(Mon_Intervention2=1,1,0)) AS CountMon_Intervention2,
Count(IIf(Mon_Intervention3=1,1,0)) AS CountMon_Intervention3,
Count(IIf(Mon_Intervention4=1,1,0)) AS CountMon_Intervention4,
Count(IIf(Tue_Intervention1=1,1,0)) AS CountTue_Intervention1, etc etc
....
FROM InterventionRecord
GROUP BY InterventionRecord.StudyNumber;

And:

SELECT InterventionRecord.StudyNumber,
Abs(Sum(Mon_Intervention1=1)) AS CountMon_Intervention1,
Abs(Sum(Mon_Intervention2=1)) AS CountMon_Intervention2,
Abs(Sum(Mon_Intervention3=1)) AS CountMon_Intervention3,
Abs(Sum(Mon_Intervention4=1)) AS CountMon_Intervention4,
Abs(Sum(Tue_Intervention1=1)) AS CountTue_Intervention1, etc etc
....
FROM InterventionRecord
GROUP BY InterventionRecord.StudyNumber;


and for both i get the error message: 'data type mismatch in criteria
expression'

any ideas of how to fix it?

Many thanks, Emma

PS if i can get them working at all, will the 'abs' work when i want it to
look at the number of '2's in the columns or '3's etc?

 

[John Spencer]

Well, what type of field is Mon_Intervention1? Is it a text field or a
number field?

If it is text
Count(IIf(Mon_Intervention1="1",1,NULL)) AS CountMon_Intervention1

Note that Count counts the presence of any value, so you need to return a
null if you don't want the count function to count the item. Anything other
than null gets counted.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

The intervention fields are text fields so that solution should work, thanks.

does this expression mean then that it modifies the 'count' function so that
it ONLY count's the "1" in the field? this is crucial because i need to have
separately queries to cound up all the 1's then all the 2's, 3's etc. thanks,
emma

 

[John Spencer]

That will count all the records where the field Mon_Intervention is equal to
"1"
If you want to count two's then just check for the value of "2"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

i was literally just writing you a reply when i got a pop up showing i'd
received this reply from you - how strange! anyway, i was writing to say
thank you so much, this has worked perfectly. Thanks again, Emma