Statistical Report to the Legislature

I have created mulitple databases and am familiar with creating your normal
reports, subtotals, averages, etc. However, now I have been charged with
creating an annual report to our state legislature generated from a statewide
database I created which gathered data from each region and area in the state
regarding Probation and Parole Officer performance on particular cases. The
primary table I have to generate the report from has 45 fileds or so with 10
being demographical and the other 35 or so being the fields I need to capture
data from, add up, and do calculations. Those 35 fields are divided into
three areas of concern to our Department and I need to add them up and get
averages and perhaps other calculations. What complicates the issue is that
some fields are true and false and some are text (bad design on my part that
I can fix for this year) and I need to screen out some values (such as Not
Applicable). My main questions is what is the best way to get the data; a
crosstab query, arrays, etc. I have looked through this forum for a while
now and I have some ideas but I am under time constraints and need to get the
report/s done. Any help on where to look (or book to buy or web site to go
to) would be appreciated. Sorry for the length of this questions. Thanks.
--
Westeral

Yes/no field are fairly easy to aggregate. To count yes in a report footer
section, use and expression like:
=Sum(Abs([YN Field]))
To count no values
=Sum([YN Field]+1)

If you have allowed freeform data input, you might want to create a make
table, totals query that groups by the field

SELECT [FF Field], First([FF Field]) AS StatisticalValue INTO tblNewFFField
FROM tblYourTable
GROUP BY [FF Field];

Make the [FF Field] in tblNewFFField a primary key and change the value in
StatisticalValue to a good value. This would allow you to change all NA, N/A,
Not Applicable, N-A in the [FF Field] to a common value of NA.

You can use the new table(s) to aggregate or even run update queries on your
original table.
--
Duane Hookom
Microsoft Access MVP


"Westeral" wrote:

I have created mulitple databases and am familiar with creating your normal
reports, subtotals, averages, etc. However, now I have been charged with
creating an annual report to our state legislature generated from a statewide
database I created which gathered data from each region and area in the state
regarding Probation and Parole Officer performance on particular cases. The
primary table I have to generate the report from has 45 fileds or so with 10
being demographical and the other 35 or so being the fields I need to capture
data from, add up, and do calculations. Those 35 fields are divided into
three areas of concern to our Department and I need to add them up and get
averages and perhaps other calculations. What complicates the issue is that
some fields are true and false and some are text (bad design on my part that
I can fix for this year) and I need to screen out some values (such as Not
Applicable). My main questions is what is the best way to get the data; a
crosstab query, arrays, etc. I have looked through this forum for a while
now and I have some ideas but I am under time constraints and need to get the
report/s done. Any help on where to look (or book to buy or web site to go
to) would be appreciated. Sorry for the length of this questions. Thanks.
--
Westeral

I haven't tried using any make table code to date. The yes/no aggregating
looks cut and dried. I have no freeform fields at all. All the Not
Applicable entries are selected from a list and are entered as Not
Applicable. Those fields have Yes, No, Not Applicable and in one question an
entry of CFA COMPAS Entry. There are 3 main areas of questions, Public
Safety (9 or so q's), Evidence Based Practices, and Statutory (legislatively
required). I will probably do 3 seperate reports looking at the 3 areas with
the most critical being PS or Public saftey. All those have just Yes/No or
text fields with Yes, No, or Not Applicable. I need to count the total of
each for the entire table (about 9000 records) and calculate an average of
Yes answers (indicating compliance by the agent) for those records that
matter (a total of the Yes and No answers as the NA ones will not apply).
Assuming no free form answers is there an easy way to get the totals for each
of the values in the records and then do the necessary calculations in the
footer? The Yes/No expressions look very easy. I hope the other is just as
easy. There will be nothing or next to nothing in the detail of the report
other than when I want to run the report by area or region rather than
statewide. Thanks.


--
Westeral


"Duane Hookom" wrote:

Yes/no field are fairly easy to aggregate. To count yes in a report footer
section, use and expression like:
=Sum(Abs([YN Field]))
To count no values
=Sum([YN Field]+1)

If you have allowed freeform data input, you might want to create a make
table, totals query that groups by the field

SELECT [FF Field], First([FF Field]) AS StatisticalValue INTO tblNewFFField
FROM tblYourTable
GROUP BY [FF Field];

Make the [FF Field] in tblNewFFField a primary key and change the value in
StatisticalValue to a good value. This would allow you to change all NA, N/A,
Not Applicable, N-A in the [FF Field] to a common value of NA.

You can use the new table(s) to aggregate or even run update queries on your
original table.
--
Duane Hookom
Microsoft Access MVP


"Westeral" wrote:

I have created mulitple databases and am familiar with creating your normal
reports, subtotals, averages, etc. However, now I have been charged with
creating an annual report to our state legislature generated from a statewide
database I created which gathered data from each region and area in the state
regarding Probation and Parole Officer performance on particular cases. The
primary table I have to generate the report from has 45 fileds or so with 10
being demographical and the other 35 or so being the fields I need to capture
data from, add up, and do calculations. Those 35 fields are divided into
three areas of concern to our Department and I need to add them up and get
averages and perhaps other calculations. What complicates the issue is that
some fields are true and false and some are text (bad design on my part that
I can fix for this year) and I need to screen out some values (such as Not
Applicable). My main questions is what is the best way to get the data; a
crosstab query, arrays, etc. I have looked through this forum for a while
now and I have some ideas but I am under time constraints and need to get the
report/s done. Any help on where to look (or book to buy or web site to go
to) would be appreciated. Sorry for the length of this questions. Thanks.
--
Westeral

Multiple yes/no or "question" columns is not really normalized. However if
you just want to be able to count the number of text values of Not
Applicable, the control source in a group or report footer might be:
=Sum(Abs([Your Field Name]="Not Applicable"))
Other fields and values would have similar expressions.

Duane Hookom
MS Access MVP

"Westeral" wrote in message
...
I haven't tried using any make table code to date. The yes/no
aggregating
looks cut and dried. I have no freeform fields at all. All the Not
Applicable entries are selected from a list and are entered as Not
Applicable. Those fields have Yes, No, Not Applicable and in one question
an
entry of CFA COMPAS Entry. There are 3 main areas of questions, Public
Safety (9 or so q's), Evidence Based Practices, and Statutory
(legislatively
required). I will probably do 3 seperate reports looking at the 3 areas
with
the most critical being PS or Public saftey. All those have just Yes/No
or
text fields with Yes, No, or Not Applicable. I need to count the total of
each for the entire table (about 9000 records) and calculate an average of
Yes answers (indicating compliance by the agent) for those records that
matter (a total of the Yes and No answers as the NA ones will not apply).
Assuming no free form answers is there an easy way to get the totals for
each
of the values in the records and then do the necessary calculations in the
footer? The Yes/No expressions look very easy. I hope the other is just
as
easy. There will be nothing or next to nothing in the detail of the
report
other than when I want to run the report by area or region rather than
statewide. Thanks.


--
Westeral


"Duane Hookom" wrote:

Yes/no field are fairly easy to aggregate. To count yes in a report
footer
section, use and expression like:
=Sum(Abs([YN Field]))
To count no values
=Sum([YN Field]+1)

If you have allowed freeform data input, you might want to create a make
table, totals query that groups by the field

SELECT [FF Field], First([FF Field]) AS StatisticalValue INTO
tblNewFFField
FROM tblYourTable
GROUP BY [FF Field];

Make the [FF Field] in tblNewFFField a primary key and change the value
in
StatisticalValue to a good value. This would allow you to change all NA,
N/A,
Not Applicable, N-A in the [FF Field] to a common value of NA.

You can use the new table(s) to aggregate or even run update queries on
your
original table.
--
Duane Hookom
Microsoft Access MVP


"Westeral" wrote:

I have created mulitple databases and am familiar with creating your
normal
reports, subtotals, averages, etc. However, now I have been charged
with
creating an annual report to our state legislature generated from a
statewide
database I created which gathered data from each region and area in the
state
regarding Probation and Parole Officer performance on particular cases.
The
primary table I have to generate the report from has 45 fileds or so
with 10
being demographical and the other 35 or so being the fields I need to
capture
data from, add up, and do calculations. Those 35 fields are divided
into
three areas of concern to our Department and I need to add them up and
get
averages and perhaps other calculations. What complicates the issue
is that
some fields are true and false and some are text (bad design on my part
that
I can fix for this year) and I need to screen out some values (such as
Not
Applicable). My main questions is what is the best way to get the
data; a
crosstab query, arrays, etc. I have looked through this forum for a
while
now and I have some ideas but I am under time constraints and need to
get the
report/s done. Any help on where to look (or book to buy or web site
to go
to) would be appreciated. Sorry for the length of this questions.
Thanks.
--
Westeral

Thanks, this will work and has saved me a lot of time. I agree the table is
not normalized as much as it should be. I was under the gun as they wanted
to track case reviews right away and I am the only one they could find to do
it. If I was starting over I would have created 3 seperate tables for the
different areas I am supposed to track. However, all the fields except the
first 9, which are demographic, are Yes/No but with 2/3's of those also
having Not Applicable as a choice with one field having one more additional
choice. All fields are required which means I don't have to deal with null
values. this was a quick and dirty database and I have to do another one
this year (completed by the end of February). I have to add some fields and
another table for other performance criteria they want to track, so I can
make some changes. I may go to 3 tables or try to normalize it better if I
can think of a way to do that. Again, thanks as this solves my immediate
problem
Westeral


"Duane Hookom" wrote:

Multiple yes/no or "question" columns is not really normalized. However if
you just want to be able to count the number of text values of Not
Applicable, the control source in a group or report footer might be:
=Sum(Abs([Your Field Name]="Not Applicable"))
Other fields and values would have similar expressions.

Duane Hookom
MS Access MVP

"Westeral" wrote in message
...
I haven't tried using any make table code to date. The yes/no
aggregating
looks cut and dried. I have no freeform fields at all. All the Not
Applicable entries are selected from a list and are entered as Not
Applicable. Those fields have Yes, No, Not Applicable and in one question
an
entry of CFA COMPAS Entry. There are 3 main areas of questions, Public
Safety (9 or so q's), Evidence Based Practices, and Statutory
(legislatively
required). I will probably do 3 seperate reports looking at the 3 areas
with
the most critical being PS or Public saftey. All those have just Yes/No
or
text fields with Yes, No, or Not Applicable. I need to count the total of
each for the entire table (about 9000 records) and calculate an average of
Yes answers (indicating compliance by the agent) for those records that
matter (a total of the Yes and No answers as the NA ones will not apply).
Assuming no free form answers is there an easy way to get the totals for
each
of the values in the records and then do the necessary calculations in the
footer? The Yes/No expressions look very easy. I hope the other is just
as
easy. There will be nothing or next to nothing in the detail of the
report
other than when I want to run the report by area or region rather than
statewide. Thanks.


--
Westeral


"Duane Hookom" wrote:

Yes/no field are fairly easy to aggregate. To count yes in a report
footer
section, use and expression like:
=Sum(Abs([YN Field]))
To count no values
=Sum([YN Field]+1)

If you have allowed freeform data input, you might want to create a make
table, totals query that groups by the field

SELECT [FF Field], First([FF Field]) AS StatisticalValue INTO
tblNewFFField
FROM tblYourTable
GROUP BY [FF Field];

Make the [FF Field] in tblNewFFField a primary key and change the value
in
StatisticalValue to a good value. This would allow you to change all NA,
N/A,
Not Applicable, N-A in the [FF Field] to a common value of NA.

You can use the new table(s) to aggregate or even run update queries on
your
original table.
--
Duane Hookom
Microsoft Access MVP


"Westeral" wrote:

I have created mulitple databases and am familiar with creating your
normal
reports, subtotals, averages, etc. However, now I have been charged
with
creating an annual report to our state legislature generated from a
statewide
database I created which gathered data from each region and area in the
state
regarding Probation and Parole Officer performance on particular cases.
The
primary table I have to generate the report from has 45 fileds or so
with 10
being demographical and the other 35 or so being the fields I need to
capture
data from, add up, and do calculations. Those 35 fields are divided
into
three areas of concern to our Department and I need to add them up and
get
averages and perhaps other calculations. What complicates the issue
is that
some fields are true and false and some are text (bad design on my part
that
I can fix for this year) and I need to screen out some values (such as
Not
Applicable). My main questions is what is the best way to get the
data; a
crosstab query, arrays, etc. I have looked through this forum for a
while
now and I have some ideas but I am under time constraints and need to
get the
report/s done. Any help on where to look (or book to buy or web site
to go
to) would be appreciated. Sorry for the length of this questions.
Thanks.
--
Westeral

Making 3 tables from the one would be IMO going in the wrong direction. You
might want to look at "At Your Survey"
http://www.rogersaccesslibrary.com/f...osts.asp?TID=3. This solution
is normalized. Each answer to each question creates a new record.
--
Duane Hookom
Microsoft Access MVP


"Westeral" wrote:

Thanks, this will work and has saved me a lot of time. I agree the table is
not normalized as much as it should be. I was under the gun as they wanted
to track case reviews right away and I am the only one they could find to do
it. If I was starting over I would have created 3 seperate tables for the
different areas I am supposed to track. However, all the fields except the
first 9, which are demographic, are Yes/No but with 2/3's of those also
having Not Applicable as a choice with one field having one more additional
choice. All fields are required which means I don't have to deal with null
values. this was a quick and dirty database and I have to do another one
this year (completed by the end of February). I have to add some fields and
another table for other performance criteria they want to track, so I can
make some changes. I may go to 3 tables or try to normalize it better if I
can think of a way to do that. Again, thanks as this solves my immediate
problem
Westeral


"Duane Hookom" wrote:

Multiple yes/no or "question" columns is not really normalized. However if
you just want to be able to count the number of text values of Not
Applicable, the control source in a group or report footer might be:
=Sum(Abs([Your Field Name]="Not Applicable"))
Other fields and values would have similar expressions.

Duane Hookom
MS Access MVP

"Westeral" wrote in message
...
I haven't tried using any make table code to date. The yes/no
aggregating
looks cut and dried. I have no freeform fields at all. All the Not
Applicable entries are selected from a list and are entered as Not
Applicable. Those fields have Yes, No, Not Applicable and in one question
an
entry of CFA COMPAS Entry. There are 3 main areas of questions, Public
Safety (9 or so q's), Evidence Based Practices, and Statutory
(legislatively
required). I will probably do 3 seperate reports looking at the 3 areas
with
the most critical being PS or Public saftey. All those have just Yes/No
or
text fields with Yes, No, or Not Applicable. I need to count the total of
each for the entire table (about 9000 records) and calculate an average of
Yes answers (indicating compliance by the agent) for those records that
matter (a total of the Yes and No answers as the NA ones will not apply).
Assuming no free form answers is there an easy way to get the totals for
each
of the values in the records and then do the necessary calculations in the
footer? The Yes/No expressions look very easy. I hope the other is just
as
easy. There will be nothing or next to nothing in the detail of the
report
other than when I want to run the report by area or region rather than
statewide. Thanks.


--
Westeral


"Duane Hookom" wrote:

Yes/no field are fairly easy to aggregate. To count yes in a report
footer
section, use and expression like:
=Sum(Abs([YN Field]))
To count no values
=Sum([YN Field]+1)

If you have allowed freeform data input, you might want to create a make
table, totals query that groups by the field

SELECT [FF Field], First([FF Field]) AS StatisticalValue INTO
tblNewFFField
FROM tblYourTable
GROUP BY [FF Field];

Make the [FF Field] in tblNewFFField a primary key and change the value
in
StatisticalValue to a good value. This would allow you to change all NA,
N/A,
Not Applicable, N-A in the [FF Field] to a common value of NA.

You can use the new table(s) to aggregate or even run update queries on
your
original table.
--
Duane Hookom
Microsoft Access MVP


"Westeral" wrote:

I have created mulitple databases and am familiar with creating your
normal
reports, subtotals, averages, etc. However, now I have been charged
with
creating an annual report to our state legislature generated from a
statewide
database I created which gathered data from each region and area in the
state
regarding Probation and Parole Officer performance on particular cases.
The
primary table I have to generate the report from has 45 fileds or so
with 10
being demographical and the other 35 or so being the fields I need to
capture
data from, add up, and do calculations. Those 35 fields are divided
into
three areas of concern to our Department and I need to add them up and
get
averages and perhaps other calculations. What complicates the issue
is that
some fields are true and false and some are text (bad design on my part
that
I can fix for this year) and I need to screen out some values (such as
Not
Applicable). My main questions is what is the best way to get the
data; a
crosstab query, arrays, etc. I have looked through this forum for a
while
now and I have some ideas but I am under time constraints and need to
get the
report/s done. Any help on where to look (or book to buy or web site
to go
to) would be appreciated. Sorry for the length of this questions.
Thanks.
--
Westeral

Thanks, I will look at this. I have used information and code from his web
site before and have it bookmarked. I believe he works at the same hospital
my daughter works at as a side note. Thanks for your help, it made my life a
lot easier in the next couple of weeks as I get these reports ready while
still doing my regular job.
--
Westeral


"Duane Hookom" wrote:

Making 3 tables from the one would be IMO going in the wrong direction. You
might want to look at "At Your Survey"
http://www.rogersaccesslibrary.com/f...osts.asp?TID=3. This solution
is normalized. Each answer to each question creates a new record.
--
Duane Hookom
Microsoft Access MVP


"Westeral" wrote:

Thanks, this will work and has saved me a lot of time. I agree the table is
not normalized as much as it should be. I was under the gun as they wanted
to track case reviews right away and I am the only one they could find to do
it. If I was starting over I would have created 3 seperate tables for the
different areas I am supposed to track. However, all the fields except the
first 9, which are demographic, are Yes/No but with 2/3's of those also
having Not Applicable as a choice with one field having one more additional
choice. All fields are required which means I don't have to deal with null
values. this was a quick and dirty database and I have to do another one
this year (completed by the end of February). I have to add some fields and
another table for other performance criteria they want to track, so I can
make some changes. I may go to 3 tables or try to normalize it better if I
can think of a way to do that. Again, thanks as this solves my immediate
problem
Westeral


"Duane Hookom" wrote:

Multiple yes/no or "question" columns is not really normalized. However if
you just want to be able to count the number of text values of Not
Applicable, the control source in a group or report footer might be:
=Sum(Abs([Your Field Name]="Not Applicable"))
Other fields and values would have similar expressions.

Duane Hookom
MS Access MVP

"Westeral" wrote in message
...
I haven't tried using any make table code to date. The yes/no
aggregating
looks cut and dried. I have no freeform fields at all. All the Not
Applicable entries are selected from a list and are entered as Not
Applicable. Those fields have Yes, No, Not Applicable and in one question
an
entry of CFA COMPAS Entry. There are 3 main areas of questions, Public
Safety (9 or so q's), Evidence Based Practices, and Statutory
(legislatively
required). I will probably do 3 seperate reports looking at the 3 areas
with
the most critical being PS or Public saftey. All those have just Yes/No
or
text fields with Yes, No, or Not Applicable. I need to count the total of
each for the entire table (about 9000 records) and calculate an average of
Yes answers (indicating compliance by the agent) for those records that
matter (a total of the Yes and No answers as the NA ones will not apply).
Assuming no free form answers is there an easy way to get the totals for
each
of the values in the records and then do the necessary calculations in the
footer? The Yes/No expressions look very easy. I hope the other is just
as
easy. There will be nothing or next to nothing in the detail of the
report
other than when I want to run the report by area or region rather than
statewide. Thanks.


--
Westeral


"Duane Hookom" wrote:

Yes/no field are fairly easy to aggregate. To count yes in a report
footer
section, use and expression like:
=Sum(Abs([YN Field]))
To count no values
=Sum([YN Field]+1)

If you have allowed freeform data input, you might want to create a make
table, totals query that groups by the field

SELECT [FF Field], First([FF Field]) AS StatisticalValue INTO
tblNewFFField
FROM tblYourTable
GROUP BY [FF Field];

Make the [FF Field] in tblNewFFField a primary key and change the value
in
StatisticalValue to a good value. This would allow you to change all NA,
N/A,
Not Applicable, N-A in the [FF Field] to a common value of NA.

You can use the new table(s) to aggregate or even run update queries on
your
original table.
--
Duane Hookom
Microsoft Access MVP


"Westeral" wrote:

I have created mulitple databases and am familiar with creating your
normal
reports, subtotals, averages, etc. However, now I have been charged
with
creating an annual report to our state legislature generated from a
statewide
database I created which gathered data from each region and area in the
state
regarding Probation and Parole Officer performance on particular cases.
The
primary table I have to generate the report from has 45 fileds or so
with 10
being demographical and the other 35 or so being the fields I need to
capture
data from, add up, and do calculations. Those 35 fields are divided
into
three areas of concern to our Department and I need to add them up and
get
averages and perhaps other calculations. What complicates the issue
is that
some fields are true and false and some are text (bad design on my part
that
I can fix for this year) and I need to screen out some values (such as
Not
Applicable). My main questions is what is the best way to get the
data; a
crosstab query, arrays, etc. I have looked through this forum for a
while
now and I have some ideas but I am under time constraints and need to
get the
report/s done. Any help on where to look (or book to buy or web site
to go
to) would be appreciated. Sorry for the length of this questions.
Thanks.
--
Westeral