Help with percentages appreciated

[C Tate]

I have a fairly basic database consisting of two main tables - clients and
assessments. In a nutshell clients get periodic assessments in which they
are given a score between 1 and 5 in 7 different areas. The database is
perhaps not normalised as well as it could be but I think it's ok for its
purpose.

The client table consists of basic fields such as firstname, surname,
dateofbirth, ethnicity, dateallocated and dateclosed. The latter two tell me
when the case was allocated to a worker and when the case was closed.

The assessment table tells me who did the assessment (cswid). It also
contains clientid, assessmentid, date of assessment (date) and then it has 7
different fields which take the scores. Their data type is number, in design
view. One of these fields is called 'schoolattendance'. If the score is 2 or
over, the client is attending school. If the score is 4 or over this means
the client is not only attending school but is in attendance around 80% of
the time.

I am trying to devise a query which will tell me which clients are are
school attenders (ie, have a score of 2 or more) and how many of these
clients have a score of 4 or more (ie, are regular attenders). I want to
report this as a percentage, eg, 55% of the clients attended school 80% of
the time. At this stage I am only reporting on open cases, that is cases
which do not have a date in the dateclosed field of the client table.

I'd really appreciated some simple guidance with this, not great detailed
sql statements as I am a complete beginner and will be totally lost with
them.

Many thanks.

 

[C Tate]

Many thanks. This looks very clever stuff! I am not sure if I am doing
something wrong but in both instances I get a message saying 'invalid
bracketing of name % school attenders'. Am I doing something wrong?

There are two ways to calculate the one area ---

SELECT Count(Assessment.clientid) AS CountOfclientid,
Sum(IIf([schoolattendance]>=2,1,0))/Count([Assessment].[clientid])*100 AS
[%
school attenders],
Sum(IIf([schoolattendance]>=4,1,0))/Count([Assessment].[clientid])*100 AS
[%
attended school 80%]
FROM Client INNER JOIN Assessment ON Client.clientid = Assessment.clientid
WHERE (((Client.dateclosed) Is Null));


SELECT Count(Assessment.clientid) AS CountOfclientid,
Sum(IIf([schoolattendance]>=2 And
[schoolattendance]<4,1,0))/Count([Assessment].[clientid])*100 AS [% school
attenders less than 80% of time],
Sum(IIf([schoolattendance]>=4,1,0))/Count([Assessment].[clientid])*100 AS
[%
attended school 80%]
FROM Client INNER JOIN Assessment ON Client.clientid = Assessment.clientid
WHERE (((Client.dateclosed) Is Null));

I have a fairly basic database consisting of two main tables - clients
and
assessments. In a nutshell clients get periodic assessments in which they
are given a score between 1 and 5 in 7 different areas. The database is
perhaps not normalised as well as it could be but I think it's ok for its
purpose.

The client table consists of basic fields such as firstname, surname,
dateofbirth, ethnicity, dateallocated and dateclosed. The latter two tell
me
when the case was allocated to a worker and when the case was closed.

The assessment table tells me who did the assessment (cswid). It also
contains clientid, assessmentid, date of assessment (date) and then it
has 7
different fields which take the scores. Their data type is number, in
design
view. One of these fields is called 'schoolattendance'. If the score is 2
or
over, the client is attending school. If the score is 4 or over this
means
the client is not only attending school but is in attendance around 80%
of
the time.

I am trying to devise a query which will tell me which clients are are
school attenders (ie, have a score of 2 or more) and how many of these
clients have a score of 4 or more (ie, are regular attenders). I want to
report this as a percentage, eg, 55% of the clients attended school 80%
of
the time. At this stage I am only reporting on open cases, that is cases
which do not have a date in the dateclosed field of the client table.

I'd really appreciated some simple guidance with this, not great detailed
sql statements as I am a complete beginner and will be totally lost with
them.

Many thanks.