If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Statistical Report to the Legislature
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 |
#3
|
|||
|
|||
Statistical Report to the Legislature
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 |
#4
|
|||
|
|||
Statistical Report to the Legislature
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 |
#5
|
|||
|
|||
Statistical Report to the Legislature
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 |
#6
|
|||
|
|||
Statistical Report to the Legislature
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 |
#7
|
|||
|
|||
Statistical Report to the Legislature
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 |
Thread Tools | |
Display Modes | |
|
|