Jim Hewson
JHewson at karta.com
Wed Aug 25 09:41:34 CDT 2004
Mark, We have a contract to do an analysis of the probability of detection (POD) of inspectors. The inspectors would "inspect" specimens similar to aircraft parts (e.g. lugs). All the data for the specimens, inspectors and inspections (including equipment used) is stored in an Access database. Statistical analysis is done to determine the POD. When developing the database we looked for reference material to help with the statistical analysis in Access. We did not find any. We purchased the "FMS Total Access Statistics" to help the analysis. Our mathematician didn't like it. We also felt it created a lot of overhead that wasn't needed. Bottom-line. We rolled our own. Statistical formulas can be broken down into their components. Then queries are created to "populate" the formula. For example, the Chi-square analysis to determine the "Coefficient of Contingency" (our mathematician's words) uses one query that calculates the components. The sequence of events for the calculations begins with a select query that uses eight tables and a form for the criteria. That query is used in another select query using six tables (some are repeated). Using the previous query, another query does some calculations. And the final query is created using the previous query to do more calculations. The final query results is the components of the formula. The formula is placed in the text box of the report. The formula is: =Sqr((1/([C1]*[R1])*(([A]-([C1]*[R1]/[N]))*([A]-([C1]*[R1]/[N]))))+(1/([C2]*[R1])*(([B]-([C2]*[R1]/[N]))*([B]-([C2]*[R1]/[N]))))+(1/([C1]*[R2])*(([C]-([C1]*[R2]/[N]))*([C]-([C1]*[R2]/[N]))))+(1/([C2]*[R2])*(([D]-([C2]*[R2]/[N]))*([D]-([C2]*[R2]/[N]))))) For our purposes this works. My suggestion is to take it step by step, slowly. Have a mathematician involved that can calculate the correct answer manually on your sample data. Determine the data you need to do the calculations and analyze the data collection process. Determine if you are actually collecting the correct data for the statistical analysis. At every step when creating the queries, determine if the results are correct. I found it was best to work backwards. Look at the formula and determine where each component should come from the data. After all the queries, criteria, etc. have been created compare the results with the mathematicians answer - do they match? If not, go back and find out why. If so, CELEBRATE! You've earned it. Triple - no - quadruple the time you think it might take to do this. Each formula could take up to a week or more to perfect. Access will do the calculations, but be prepared to add user interface feedback. One report that has several sub reports that have numerous calculations (using many queries) has taken several minutes to finish. HTH Best of luck. Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Mitsules, Mark S. (Newport News) Sent: Wednesday, August 25, 2004 8:14 AM To: [AccessD] Subject: [AccessD] Reference Material Group, Can anyone suggest some reference material that discusses developing sophisticated Access reports which will include statistical analysis and graphing? Mark -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com