Summary Statistics in Access or Excel

[Pipsqueaker]

Hi I'm fairly new to Access. I've used Excel a long time but never felt I needed Access until now. I need guidance from experience Access users. I'm designing a new database, and I'm interested in using queries to provide summary statistics of a subset of records. Each record represents an event. The statistics that I know I'll use over and over again are probabilities, geometric means, and geometric variances. Would this be best accomplished using queries and reports? Alternatively should I use queries to get the subset of data and then export it to Excel to calculate my statistics? Could you please point me to more information about doing this within Access if it's possible? If it's best done in Excel, could you point me to information about exporting to Excel in an automated way.

[ArviLaanemets]

You'll have all needed source data in Access tables. Then you create saved queries based on those tables, which return wanted statistics (preferably general enough ones, so you can get more flexible at reporting using filtering conditions, and probably with some [calculated] fields you can use to set those filters).
From there on, you can either:
a) Use Access reports. When going this path, I have usually designed a Reports page, where user can e.g. select a report from predefined list (there can be several setups for different reports, every one with it's own combos for selecting report and conditions for it, and command button to run the report), and set filter conditions for selected report, and then preview or print it from command button. The button event creates the command string to run the selected report with filter conditions applied, and executes it.
b) Use ODBC query in Excel Report workbook to read Access query into Excel table (you can enter/select filtering conditions for this ODBC query e.g. on top of worksheet where you want the result table to be read in, or where you want the graph to be designed). You can have any reasonable number of worksheets for different reports in same Excel file. You set queries to be refreshed on opening, you can set the query to be refreshed whenever any of report conditions was changed, and user can always refresh manually the query on active worksheet, or all queries in workbook.

Which of these possibilities you use, is up to you (i.e. use what you like more, or what gives the better result). I myself mostly use Excel reports, because I usually use graphs for such reports and I prefer Excel graphs (and some 20 years ago almost everyone had Excel, and almost nobody did have Access).