Need to Perform Statistical Operations

[newtonboy]

Hi All,

I am new to Access programmimg, but am to do a job in Access/Excel. What i have to do is to develop a database which will keep records of daily operations of a plant.Data is not all that much...Input, Output and 2 other columns at teh most. With these data I have to perform Statistical operations to be displayed on screen.

I intend to do this in Excel as it contains the Mathematical functions I need, even though I realize that Access is the Database guru ! Can I use Excel as Database and create forms, then perform mathematical functions and display it on the screen ? I do not mind doing it in Access if there is a better/easier method. I guess If i do it in access I will have to make access talk to excel for the maths functions.......

I am not sure of the best method....and how to do what I have just described......I would apperciate any help in thsi regard.....Looking forward to replies.

Regards,
Newton

 

[Pat Hartman]

Are you certain that Access doesn't contain the statistical functions that you need?

Access is a far better tool to use as a database than Excel. If it turns out that Access doesn't contain the functions that you need, you can export data to Excel quite easily. To make the Access/Excel model work easily, always export (or import) the data to a separate workbook. Then have the workbook with the calculations reference the workbook that contains the data.

 

[The_Doc_Man]

If you open a code module and then follow path Tools >> References (FROM THE CODE WINDOW'S MENU BAR) you can make a reference to Excel's library. When you do that, all of the Excel functions become available and you can get help on them.

But there is another thought...

In Excel, your statistical functions other than some financial specialties are already available in Access - but you have to start thinking in terms of queries and perhaps reports with grouping. Because most of the stats in Excel are, like, gimme a total of this column. Gimme the maximum value from that column. Lemme see the standard deviation from that column. Every one of those can be done in a query using the SQL Aggregate functions.

If you wanted more elegant statistics than those, you can get them the way I said - through references. But I'm betting you don't need the fanciest ones. You just need to develop the mindset that a spreadsheet and a table aren't the same thing. Then you can make queries do what you want them to do.

 

[Pat Hartman]

I forgot to post this link. I created a spreadsheet that lists all/most of the Access functions. You can use it to see if the functions you need are available.

Access Function Glossary

 

[newtonboy]

Thanx for the reply. I have actually been looking to use Multiple regression technique on the data collected by me. Still not sure if Access can do it for me. But I will search the XL sheet, it might just contain this.......Hoping for it . Thanx anyway.

 

[KKilfoil]

Thanx for the reply. I have actually been looking to use Multiple regression technique on the data collected by me. Still not sure if Access can do it for me. But I will search the XL sheet, it might just contain this.......Hoping for it . Thanx anyway.

As far as I could find, Access does NOT allow for MLR 'out-of-the box', although you CAN buy some addins that do it for you. I have gotten around that by
1) exporting the relevant data to Excel, and crunching the numbers there
2) developing a general 'module' that uses Simplex Optimization to solve virtually any non-linear equation for several unknowns to minimize 'least-squares error'.

If you are sure that your solution is only going to require multiple LINEAR regression, I would do (1) above, as it is much easier on the brain.